PDH-Item FUTURE_HIERARCHY EFF Attribute Report-Bursting -Ess Job

Report Parameters

-----------------

P_Doc_id :(UCM Content ID)

p_doc_approver :(UCM Content File Uploader/Author)

Parameters Mapping :

--------------------

AND    (
              eseb.attribute_char13 IN ( :P_DOC_ID )
       OR     coalesce(:P_DOC_ID, NULL)IS NULL )
AND    (
              eseb.attribute_char14 IN ( :P_DOC_AUTHOR)
       OR     coalesce(:P_DOC_AUTHOR, NULL)IS NULL )

Report query

---------------

SELECT esi.item_number      product_id,
       eseb.attribute_char3 product_type,
       eseb.attribute_char7 effective_date,
       eseb.attribute_char5 reason_code,
       eseb.attribute_char4 list_price_impact,
       decode(eseb.attribute_char6,
              'Orderable-Decrease','D',
              'Orderable-Increase','I',
              'Non-Orderble','E') increase_decrease,
       eseb.attribute_char8       buc_approver,
       eseb.attribute_char13      doc_id
FROM   ego_item_eff_b eseb,
       egp_system_items_b esi,
       egp_item_classes_tl eict,
       inv_org_parameters iop,
       egp_system_items_tl esit
WHERE  1 = 1
       --And esi.Item_number ='APR5PID2'
AND    eseb.inventory_item_id = esi.inventory_item_id
AND    eseb.organization_id = esi.organization_id
AND    eict.item_class_id = esi.item_catalog_group_id
AND    iop.organization_id = esi.organization_id
AND    esit.inventory_item_id = esi.inventory_item_id
AND    esit.organization_id = esi.organization_id
AND    esit.LANGUAGE = 'US'
AND    eict.LANGUAGE = 'US'
AND    eseb.acd_type = 'PROD'
AND    esi.acd_type = 'PROD'
AND    iop.organization_code='GLO'
AND    eict.item_class_name='Billing'
AND    eseb.context_code = 'FUTURE_HIERARCHY'
AND    (
              eseb.attribute_char13 IN ( :P_DOC_ID )
       OR     coalesce(:P_DOC_ID, NULL)IS NULL )
AND    (
              eseb.attribute_char14 IN ( :P_DOC_AUTHOR)
       OR     coalesce(:P_DOC_AUTHOR, NULL)IS NULL )

--------------------Bursting Query

SELECT doc_id                                                   AS KEY,
       'CSCO_FUT_HEIRARCHY_RPT_1'                               template,
       'en-US'                                                  locale,
       'XLSX'                                                   output_format,
       'EMAIL'                                                  del_channel,
       'Hierarchy Mass Move Change Request Report'
       ||' : '
       ||To_char(SYSDATE, 'DD-MM-YYYY')                         output_name,
       attribute_char14                                         parameter1,
       'apps2cloudfusion@gmail.com,apps2cloudfusion@gmail.com,apps2cloudfusion@gmail.com' parameter2,
       'apps2cloudfusion@gmail.com'                                     parameter3,
       'Hierarchy Mass Move Change Request Report'              parameter4,
--Subject 
'Hi,   PFA for the Hierarchy Mass Move Change Request Report. Report contains the PGC impact of hierarchy change. Document ID'
||' : '
||doc_id
||'   '
|| 'Thanks, PMDM-PDH Cloud'                              parameter5,
'true'                                                   parameter6
FROM   (SELECT esi.item_number                                   product_id,
               eseb.attribute_char3                              product_type,
               eseb.attribute_char7                              effective_date,
               eseb.attribute_char5                              reason_code,
               eseb.attribute_char4
               list_price_impact,
               Decode(eseb.attribute_char6, 'Orderable-Decrease', 'D',
                                            'Orderable-Increase', 'I',
                                            'Non-Orderble', 'E')
               increase_decrease,
               eseb.attribute_char13                             doc_id,
               eseb.attribute_char14
        FROM   ego_item_eff_b eseb,
               egp_system_items_b esi,
               egp_item_classes_tl eict,
               inv_org_parameters iop,
               egp_system_items_tl esit
        WHERE  1 = 1 --And esi.Item_number ='APR5PID2'
               AND eseb.inventory_item_id = esi.inventory_item_id
               AND eseb.organization_id = esi.organization_id
               AND eict.item_class_id = esi.item_catalog_group_id
               AND iop.organization_id = esi.organization_id
               AND esit.inventory_item_id = esi.inventory_item_id
               AND esit.organization_id = esi.organization_id
               AND esit.LANGUAGE = 'US'
               AND eict.LANGUAGE = 'US'
               AND eseb.acd_type = 'PROD'
               AND esi.acd_type = 'PROD'
               AND iop.organization_code = 'GLO'
               AND eict.item_class_name = 'Billing'
               AND eseb.context_code = 'FUTURE_HIERARCHY'
               AND eseb.attribute_char7 IS NULL
               AND ( eseb.attribute_char13 IN ( :P_DOC_ID )
                      OR Coalesce(:P_DOC_ID, NULL)IS NULL )
               AND ( eseb.attribute_char14 IN ( :P_DOC_AUTHOR )
                      OR Coalesce(:P_DOC_AUTHOR, NULL)IS NULL )) 

--------Submit ESS Job From Dell Bhoomi/Soap UI

https://<<Instance>>:443/fscmService/ErpIntegrationService?WSDL

<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
<soapenv:Header/>
<soapenv:Body>
<typ:submitESSJobRequest>
<typ:jobPackageName>/oracle/apps/ess/custom/A2CF_Custom/SCM/Integrations/Cs_GetItem_Report/</typ:jobPackageName>
<typ:jobDefinitionName>Itemfuturera</typ:jobDefinitionName>
<!--Zero or more repetitions:-->
<typ:paramList>apps2cloudfusion@gmail.com</typ:paramList>
<typ:paramList>UCMFA01690885</typ:paramList>
</typ:submitESSJobRequest>
</soapenv:Body>
</soapenv:Envelope>

------MultiSheet Layout (RTF with Excel Template )

<?spreadsheet-sheet-name: {‘Future Hierarchy’}?>

Product ID	Product Family. Product Type. Product Subgroup	Effective Date	Reason Code	List Price Impact	Increase Decrease
F PRODUCT_ID	PRODUCT_TYPE	EFFECTIVE_DATE	REASON_CODE	LIST_PRICE_IMPACT	INCREASE_DECREASE E

using page break to get next sheet in excel

 

<?spreadsheet-sheet-name: {‘Increase Decrease Dates’}?>

Increase Cadence Date	Decrease Cadence Date
F INCREASE_CADENCE_DATE	DECREASE_CADENCE_DATE E