CREATE OR REPLACE PACKAGE BODY INTLECM.PRODUCT_UTF_CHECK AS
G_FILE UTL_FILE.FILE_TYPE;
DEBUG_PROCESS_ID LOG_PARAMETERS.PROCESS_ID%TYPE;
PROCEDURE NL IS
BEGIN
UTL_FILE.NEW_LINE(G_FILE);
END NL;
FUNCTION ITE (B BOOLEAN, T NVARCHAR2, F NVARCHAR2) RETURN NVARCHAR2 IS
BEGIN
IF B THEN
RETURN T;
ELSE
RETURN F;
END IF;
END ITE;
PROCEDURE P (P_TEXT IN VARCHAR2) IS
BEGIN
--UTL_FILE.PUT_LINE_NCHAR(G_FILE, P_TEXT);
UTL_FILE.PUT_LINE_NCHAR(G_FILE, CONVERT(TO_NCHAR(P_TEXT), 'WE8MSWIN1252', 'AL16UTF16'));
END P;
FUNCTION TAG (P_TAG VARCHAR2, P_TEXT NVARCHAR2 DEFAULT NULL) RETURN NVARCHAR2 IS
BEGIN
RETURN '<' ||TRIM (P_TAG) || '>' || ITE(TRIM(P_TEXT) IS NOT NULL, TRIM(HTF.ESCAPE_SC(P_TEXT)), NULL) || '</' || TRIM (P_TAG) || '>';
END TAG;
PROCEDURE PRODUCT (P_REGION IN NVARCHAR2 DEFAULT NULL) IS
CURSOR CURSOR01 IS
SELECT EP.*,
DECODE(P1.ISBN, NULL, 0, 1) SUBSTITUTION_VALID_PDH,
DECODE(P2.ISBN, NULL, 0, 1) PRIOR_EDITION_VALID_PDH
FROM TMP_UTF8_PRODUCT EP,
INTLDBO.PRODUCT IP,
INTLDBO.PRODUCT P1,
INTLDBO.PRODUCT P2,
INTLDBO.SELLING_RIGHTS SR
WHERE EP.MHID = IP.ISBN(+)
AND EP.SUBSTITUTION_MHID = P1.ISBN(+)
AND EP.PRIOR_EDITION_MHID = P2.ISBN(+)
AND EP.SELLING_RIGHTS_CODE = SR.SELLING_RIGHTS_CODE(+)
AND REGION = DECODE(P_REGION, NULL, REGION, P_REGION)
AND (TRUNC(EP.DATE_ADDED) = TRUNC(SYSDATE) OR TRUNC(EP.DATE_MODIFIED) = TRUNC (TO_DATE( '01-05-2020', 'DD-MM-YYYY' ), 'DD'))
AND EP.MHID NOT IN (SELECT MHID FROM ECM_NOLOAD)
AND ((EP.AUTHOR IS NOT NULL AND EP.EDITION IS NOT NULL AND EP.COPYRIGHT_DATE IS NOT NULL AND IP.ISBN IS NULL
AND (EP.SELLING_RIGHTS_CODE IS NULL OR SR.SELLING_RIGHTS_CODE IS NOT NULL)
) OR IP.ISBN IS NOT NULL)
AND ((IS_ISBN10(EP.MHID) = 0 AND EP.ISBN IS NOT NULL) OR IS_ISBN10(EP.MHID) = 1)
ORDER BY EP.REGION, EP.ISBN;
BEGIN
G_FILE := UTL_FILE.FOPEN_NCHAR ('INTLECM_OUTBOUND', 'NITESHproduct' || TRIM(UPPER(P_REGION)) || '.xml', 'w', 32767);
-- UTL_FILE.PUT_LINE_NCHAR(G_FILE, convert('NITESHproduct.xml', 'WE8MSWIN1252', 'AL16UTF16'));
P('<?xml version="1.0" encoding="UTF-8"?>');
NL;
P('<e:LoadItemListEBM');
P('xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"');
P('xmlns="http://xmlns.mheducation.com/EnterpriseObjects/Core/CommonEBO/V1"');
P('xmlns:a="http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2"');
P('xmlns:d="http://xmlns.mheducation.com/EnterpriseObjects/Core/Common/V1"');
P('xmlns:e="http://xmlns.mheducation.com/EnterpriseObjects/Core/EBO/Item/V1"');
P('xmlns:f="http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2"');
P('xmlns:g="http://xmlns.oracle.com/EnterpriseObjects/Core/CommonEBO/V1"');
P('xsi:schemaLocation="http://xmlns.mheducation.com/EnterpriseObjects/Core/EBO/Item/V1 EOL/mheducation/EnterpriseObjectLibrary/Core/EBO/Item/V1/ItemEBM.xsd');
P(' http://xmlns.mheducation.com/EnterpriseObjects/Core/EBO/Item/V1 EOL/mheducation/EnterpriseObjectLibrary/Core/EBO/Item/V1/ItemEBO.xsd');
P(' http://xmlns.mheducation.com/EnterpriseObjects/Core/CommonEBO/V1 EOL/mheducation/EnterpriseObjectLibrary/Core/CommonEBO/V1/Item.xsd');
P(' http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2 EOL/oracle/EnterpriseObjectLibrary/Infrastructure/V1/Meta.xsd ">');
NL;
P('<a:EBMHeader xmlns="http://xmlns.oracle.com/EnterpriseObjects/Core/Common/V2">');
P(TAG('CreationDateTime', TO_CHAR(CAST(SYSTIMESTAMP AT TIME ZONE 'UTC' AS DATE), 'YYYY-MM-DD"T"HH24:MI:SS"Z"')));
P('<Sender>');
P(' <ID>INTLECM</ID>');
P(' <Description>International Product load</Description>');
P('</Sender>');
P('</a:EBMHeader>');
NL;
P('<e:DataArea>');
P('<e:LoadItemList>');
NL;
FOR C1 IN CURSOR01 LOOP
P('<OrganizationItem Name="' || C1.REGION || '">');
--Name
IF IS_ISBN10(C1.MHID) = 1 THEN
P(TAG('Name', C1.MHID));
END IF;
-- P('<f:Status>');
-- P(TAG('f:Code', C1.OPIM_PUB_STATUS_CODE));
-- IF C1.PUB_STATUS_DATE IS NOT NULL THEN
-- P(TAG('f:EffectiveDateTime', TO_CHAR(C1.PUB_STATUS_DATE, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')));
-- END IF;
-- P('</f:Status>');
IF C1.ISBN IS NOT NULL THEN
P('<f:ItemIdentification><f:AlternateObjectKey><f:ID schemeID="ISBN13">' || C1.ISBN || '</f:ID></f:AlternateObjectKey></f:ItemIdentification>');
END IF;
--Description
--corecom:Status
--TypeCode
--BaseUOMCode
--corecom:ItemIdentification
--AuthorID
--ProjectNumber
--ProgramISBN
--LegacyStatSponsorCode
IF C1.STAT_SPONSOR IS NOT NULL THEN
P(TAG('LegacyStatSponsorCode', C1.STAT_SPONSOR));
END IF;
--LegacyProductGroupCode
IF C1.PRODUCT_GROUP IS NOT NULL THEN
P(TAG('LegacyProductGroupCode', C1.PRODUCT_GROUP));
END IF;
--ItemDetails
P('<ItemDetails>');
P(TAG('ISBNRequiredFlag', ITE(C1.ISBN IS NOT NULL, 'Y', 'N')));
P(TAG('EBSFullTitle', SUBSTRB(TO_CHAR(C1.LONG_TITLE), 1, 80)));
P(TAG('EBSShortTitle', SUBSTRB(TO_CHAR(C1.SHORT_TITLE), 1, 30)));
IF C1.EDITION IS NOT NULL THEN
P(TAG('Edition', C1.EDITION));
END IF;
IF C1.AUTHOR IS NOT NULL THEN
P(TAG('LeadContributor', SUBSTRB(TO_CHAR(C1.AUTHOR), 1, 15)));
END IF;
P(TAG('CopyrightYear', C1.COPYRIGHT_DATE));
P('<IntlTitleInformation>');
P(TAG('IntlTitle', C1.INTERNATIONAL_TITLE));
-- IF C1.INTERNATIONAL_LEAD_CONTRIBUTOR IS NOT NULL THEN
-- P(TAG('IntlLeadContributor', SUBSTRB(TO_CHAR(C1.INTERNATIONAL_LEAD_CONTRIBUTOR), 1, 40)));
-- END IF;
P('</IntlTitleInformation>');
P('</ItemDetails>');
--ItemProgramCharacteristics
IF C1.TITLE_TYPE IS NOT NULL THEN
P('<ItemProgramCharacteristics>');
P(TAG('TitleType', C1.TITLE_TYPE));
P('</ItemProgramCharacteristics>');
END IF;
--ItemSourceSystemInformation
IF IS_ISBN10(C1.MHID) = 0 THEN
P('<ItemSourceSystemInformation>');
P(TAG('SourceId', C1.MHID));
P('</ItemSourceSystemInformation>');
END IF;
--ItemPhysicalCharacteristics
P('<ItemPhysicalCharacteristics>');
P('<HeightMeasureSI unitCode="cm">' || TRIM(TO_CHAR(C1.PRODUCT_HEIGHT, '99990.9999')) || '</HeightMeasureSI>');
P('<LengthMeasureSI unitCode="cm">' || TRIM(TO_CHAR(C1.PRODUCT_LENGTH, '99990.9999')) || '</LengthMeasureSI>');
P('<WidthMeasureSI unitCode="cm">' || TRIM(TO_CHAR(C1.PRODUCT_WIDTH, '99990.9999')) || '</WidthMeasureSI>');
P('<WeightMeasureSI unitCode="gm">' || TRIM(TO_CHAR(C1.PRODUCT_WEIGHT, '99990.9999')) || '</WeightMeasureSI>');
P('<ItemTextCharacteristics><NumberOfPages>' || C1.NUMBER_OF_PAGES || '</NumberOfPages></ItemTextCharacteristics>');
P('</ItemPhysicalCharacteristics>');
--InventoryCharacteristics
IF C1.AVAILABLE_QUANTITY IS NOT NULL THEN
P('<InventoryCharacteristics>');
P('<SupplyAttributes>');
P(TAG('AvailableQty', C1.AVAILABLE_QUANTITY));
P('</SupplyAttributes>');
P('</InventoryCharacteristics>');
END IF;
--ItemPurchasingCharacteristics
--ItemPlanningCharacteristics
--ItemManufacturingCharacteristics
--ItemContainerCharacteristics
IF C1.CARTON_QUANTITY IS NOT NULL THEN
P('<ItemContainerCharacteristics>');
P(TAG('CartonQuantity', C1.CARTON_QUANTITY));
P('</ItemContainerCharacteristics>');
END IF;
--ItemOrderManagementCharacteristics
P('<ItemOrderManagementCharacteristics>');
P('<ProcessingCapabilitiess>');
IF C1.PUBLICATION_DATE IS NOT NULL THEN
P(TAG('PublicationDate', TO_CHAR(C1.PUBLICATION_DATE, 'YYYY-MM-DD')));
END IF;
IF C1.INSTRUCTOR_MANUAL_INDICATOR IS NOT NULL THEN
P(TAG('InstructorMaterialIndicator', ITE(C1.INSTRUCTOR_MANUAL_INDICATOR = 'Y', 'true', 'false')));
END IF;
IF C1.SELLING_RIGHTS_CODE IS NOT NULL THEN
P(TAG('SellingRights', C1.SELLING_RIGHTS_CODE));
END IF;
P('</ProcessingCapabilitiess>');
IF C1.CONTRACT_ONLY_INDICATOR IS NOT NULL THEN
P('<FulfillmentSystemResctrictions>');
P(TAG('ContractCustomersOnlyIndicator', ITE(C1.CONTRACT_ONLY_INDICATOR = 'Y', 'true', 'false')));
P('</FulfillmentSystemResctrictions>');
END IF;
P('</ItemOrderManagementCharacteristics>');
--ItemServiceCharacteristics
--InventoryLocation
--ItemOwningCharaceteristics
P('<ItemOwningCharaceteristics>');
P(TAG('Subdivision', C1.SUBDIVISION_CODE));
IF C1.AREA IS NOT NULL THEN
P(TAG('Area', C1.AREA));
END IF;
IF C1.DISCIPLINE IS NOT NULL THEN
P(TAG('Discipline', C1.DISCIPLINE));
END IF;
P('</ItemOwningCharaceteristics>');
--ItemPricingCharacteristics
--ItemRegionalPricingCharacteristics
--ItemMarketingCharacteristics
IF C1.MARKETING_TITLE IS NOT NULL THEN
P('<ItemMarketingCharacteristics>');
P('<MarketingTitleInfo>');
P(TAG('MarketingTitle', C1.MARKETING_TITLE));
P('</MarketingTitleInfo>');
P('</ItemMarketingCharacteristics>');
END IF;
--ItemPublishingIndicators
IF C1.PUBNET_INDICATOR IS NOT NULL THEN
P('<ItemPublishingIndicators>');
P(TAG('PubnetPublishIndicator', ITE(C1.PUBNET_INDICATOR = 'Y', 'true', 'false')));
P('</ItemPublishingIndicators>');
END IF;
--ItemPublisherDetails
--ItemPublishingStatus
P('<ItemPublishingStatus>');
P(TAG('PublicationStatus', C1.OPIM_PUB_STATUS_CODE));
IF C1.PUB_STATUS_DATE IS NOT NULL THEN
P(TAG('StatusDate', TO_CHAR(C1.PUB_STATUS_DATE, 'YYYY-MM-DD')));
END IF;
P('</ItemPublishingStatus>');
--ItemAccountingCharacteristics
--corecomEBO:ItemClassification
--corecomEBO:RelatedItem
IF C1.PRIOR_EDITION_MHID IS NOT NULL AND C1.PRIOR_EDITION_VALID = 1 THEN
P('<g:RelatedItem>');
P('<g:RelationshipCode>Prior Edition</g:RelationshipCode>');
P('<a:ItemReference>');
P('<a:ItemIdentification>');
P('<a:ID schemeAgencyID="MHE" schemeID="MHID">' || C1.PRIOR_EDITION_MHID || '</a:ID>');
P('</a:ItemIdentification>');
P('</a:ItemReference>');
P('</g:RelatedItem>');
END IF;
--ItemInvestmentCharacteristics
--ItemUsageCharacteristics
--ItemRoyaltyAndPermissionsCharacteristics
--corecomEBO:ItemCatalog
--corecommhe:CrossReference
--ItemContributors
--ItemTaxonomy
--CRMFlags
--EditorialInfoOnlineSites
--ProductContacts
--MediaReviews
--SupplementProductLinkages
--InternalCompetitionProducts
--ExternalCompetitionProducts
--SubstitutionInformation
IF C1.SUBSTITUTION_MHID IS NOT NULL AND C1.SUBSTITUTION_VALID = 1 THEN
P('<SubstitutionInformation>');
P('<SubstituteToItem>' || C1.SUBSTITUTION_MHID || '</SubstituteToItem>');
P('<SubstitutionTypeCode>W</SubstitutionTypeCode>');
P('</SubstitutionInformation>');
END IF;
--MilestoneDates
--InternationalPublishRestrictions
P('<InternationalPublishRestrictions>');
IF C1.ESALESENABLED IS NOT NULL THEN
P(TAG('eSalesEnabled', ITE(C1.ESALESENABLED = 1, 'true', 'false')));
END IF;
IF C1.SAMPLEFROMCRMENABLED IS NOT NULL THEN
P(TAG('SampleFromCRMEnabled', ITE(C1.SAMPLEFROMCRMENABLED = 1, 'true', 'false')));
END IF;
IF C1.OPENUPENABLED IS NOT NULL THEN
P(TAG('OpenUniversityPressEnabled', ITE(C1.OPENUPENABLED = 1, 'true', 'false')));
END IF;
IF C1.ECOMMERCEENABLED IS NOT NULL THEN
P(TAG('eCommerceEnabled', ITE(C1.ECOMMERCEENABLED = 1, 'true', 'false')));
END IF;
P('</InternationalPublishRestrictions>');
--LastOrderReceived
--ComponentItems
--Features
--CopyMirrorAttributes
--CustomProductDevelopment
IF C1.DURATION IS NOT NULL THEN
P('<SubscriptionSpecifications>');
P(TAG('Duration', C1.DURATION));
P(TAG('DurationPeriod', C1.DURATION_PERIOD));
P('</SubscriptionSpecifications>');
END IF;
--Adding SubscriptionSpecifications
P('</OrganizationItem>');
NL;
END LOOP;
P('</e:LoadItemList>');
P('</e:DataArea>');
P('</e:LoadItemListEBM>');
UTL_FILE.FFLUSH (G_FILE);
UTL_FILE.FCLOSE (G_FILE);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(G_FILE) THEN
UTL_FILE.FFLUSH(G_FILE);
UTL_FILE.FCLOSE(G_FILE);
END IF;
UTL_DEBUG.LOG_MESSAGE (
PROCESS_ID => DEBUG_PROCESS_ID,
OBJECT_NAME => 'PRODUCT_UTF_CHECK.PRODUCT',
MESSAGE_TYPE => 'E',
LOG_MESSAGE => SQLERRM || UTL_TCP.CRLF || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
SQLCODE => SQLCODE,
SQLERRM => SQLERRM);
END PRODUCT;
BEGIN
DEBUG_PROCESS_ID := UTL_DEBUG.INITIALIZE_DEBUG('NITESH');
END;
/