PO Substitute Receipts
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Substitute Receipts Report
Application: Purchasing
Source: Substitute Receipts Report (XML)
Short Name: POXRVRSR_XML
DB package: PO_POXRVRSR_XMLP_PKG
Description: Substitute Receipts Report
Application: Purchasing
Source: Substitute Receipts Report (XML)
Short Name: POXRVRSR_XML
DB package: PO_POXRVRSR_XMLP_PKG
SELECT poh.segment1||decode(por.release_num,null,null,'-'||por.release_num) Document_Number , T.type_name Document_Type , pov.vendor_name || decode( pvs.vendor_site_code ,null,null,',' || pvs.vendor_site_code ) Source , papf1.full_name Buyer , TO_CHAR(poh.creation_date, 'DD/MON/YYYY HH24:MI:SS') Creation_Date , decode(psp.manual_po_num_type,'NUMERIC',null,poh.segment1) Document_Numbering1 , decode(psp.manual_po_num_type,'NUMERIC',to_number(poh.segment1),null) Document_Numbering2 , pol.line_num Line , null C_FLEX_ITEM_ORD , pol.item_revision Rev , pol.item_description Description , null C_FLEX_ITEM_SUB , rct.po_revision_num Rev1 , rsl.item_description Description1 , round(rct.quantity,:P_qty_precision) Received , rct.unit_of_measure Unit , papf.full_name Receiver , lot.location_code Location , TO_CHAR(rct.transaction_date, 'DD/MON/YYYY HH24:MI:SS') TDate , rsh.receipt_num Receipt_Number , substr(rct.comments,1,240) Comments , plc.displayed_field Displayed_Source_Type , ood.organization_code Org, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_ord_disp', 'INV', 'MSTK', 101, MSI1.ORGANIZATION_ID, MSI1.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_ORD_DISP, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_sub_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_SUB_DISP FROM mtl_system_items msi , mtl_system_items msi1 , PER_ALL_PEOPLE_F PAPF , PER_ALL_PEOPLE_F PAPF1 , hr_locations_all_tl lot , po_lines_all pol , po_lookup_codes plc , PO_DOCUMENT_TYPES_ALL_TL T , PO_DOCUMENT_TYPES_ALL_B B , po_system_parameters_all psp , po_releases_all por , po_vendor_sites_all pvs , po_vendors pov , po_headers poh , rcv_shipment_lines rsl , rcv_shipment_headers rsh , rcv_transactions rct , org_organization_definitions ood WHERE poh.org_id = pvs.org_id AND poh.org_id = psp.org_id AND poh.vendor_id = pov.vendor_id AND poh.vendor_site_id = pvs.vendor_site_id (+) AND PAPF1.BUSINESS_GROUP_ID = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID),0) FROM FINANCIALS_SYSTEM_PARAMETERS FSP) AND TRUNC(SYSDATE) BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_PERSON (PAPF1.person_id ,PAPF1.current_applicant_flag ,PAPF1.current_employee_Flag ,PAPF1.current_npw_flag ,PAPF1.employee_number ,PAPF1.applicant_number ,PAPF1.npw_number)) = 'TRUE' AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF1.BUSINESS_GROUP_ID , HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF1.BUSINESS_GROUP_ID AND poh.agent_id = PAPF1.person_id AND rct.po_header_id = poh.po_header_id AND rct.po_line_id = pol.po_line_id AND rct.po_release_id = por.po_release_id (+) AND rct.shipment_line_id = rsl.shipment_line_id AND rct.shipment_header_id = rsh.shipment_header_id AND rct.substitute_unordered_code = 'SUBSTITUTE' AND rct.transaction_type = 'RECEIVE' AND rsl.item_id = msi.inventory_item_id(+) AND pol.item_id = msi1.inventory_item_id(+) AND nvl(msi.organization_id,:c_organization_id) = :c_organization_id AND nvl(msi1.organization_id,:c_organization_id) = :c_organization_id AND rct.employee_id = papf.person_id (+) and trunc(sysdate) between pAPF.effective_start_date (+) and pAPF.effective_end_date (+) and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_PERSON (PAPF.person_id (+),PAPF.current_applicant_flag (+),PAPF.current_employee_Flag (+),PAPF.current_npw_flag (+),PAPF.employee_number (+),PAPF.applicant_number (+),PAPF.npw_number(+))) = 'TRUE' AND decode(hr_general.get_xbg_profile,'Y', papF.business_group_id (+), hr_general.get_business_group_id) = papF.business_group_id (+) AND rct.location_id = LOT.location_id(+) AND LOT.LANGUAGE(+) = USERENV('LANG') AND plc.lookup_type = 'SHIPMENT SOURCE TYPE' AND plc.lookup_code = rsh.receipt_source_code AND B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE AND B.ORG_ID = T.ORG_ID AND B.ORG_ID = POH.ORG_ID AND T.LANGUAGE = USERENV('LANG') AND poh.type_lookup_code = B.document_subtype AND B.document_type_code in ('PO', 'PA') AND rct.transaction_date BETWEEN nvl(:P_trans_date_from,rct.transaction_date - 1) AND nvl(:P_trans_date_to, rct.transaction_date + 1) AND rct.organization_id = nvl(:P_org_id,rct.organization_id) AND ood.organization_id = rct.organization_id ORDER BY Document_Number , DISPLAYED_SOURCE_TYPE , BUYER , CREATION_DATE , DOCUMENT_TYPE , SOURCE , DOCUMENT_NUMBERING1 , DOCUMENT_NUMBERING2 , ORG , LINE |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Title |
|
Char | |
Receipt Date From |
|
DateTime | |
To |
|
DateTime | |
Organization Name |
|
LOV Oracle | |
Dynamic Precision Option |
|
LOV Oracle |