PO Receiving Account Distribution
Description
- Categories
- BI Publisher
- Application
- Purchasing
- Source
- Receiving Account Distribution Report (XML)
- Short Name
- POXRRVDR_XML
- DB package
- PO_POXRRVDR_XMLP_PKG
Imported from BI Publisher
Receiving Account Distribution Report
Receiving Account Distribution Report
SQL 253 lines
SELECT 1 C_dummy_break, rdv.po_number po_number, rdv.code_combination_id ccid, null C_FLEX_ITEM, null C_FLEX_ACC, null C_FLEX_CAT, rdv.item_id item_id, rdv.transaction_id trx_id, rdv.accounting_date acct_date, round((decode(rdv.event_type_id, 15, DECODE(accounting_line_type, 'Receiving Inspection', 1, -1), 16, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 17, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), Decode(rdv.accounting_line_type, 'Landed Cost Absorption', Sign(po_unit_price - unit_landed_cost), 1) * decode(rdv.entered_dr,NULL, -1, 1)) * rdv.primary_quantity) * rdv.source_doc_quantity / rdv.total_source_doc_quantity, :P_qty_precision) qty, decode(rdv.event_type_id, 15, DECODE(accounting_line_type, 'Receiving Inspection', 1, -1), 16, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 17, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), Decode(rdv.accounting_line_type, 'Landed Cost Absorption', Sign(po_unit_price - unit_landed_cost), 1) * decode(rdv.entered_dr,NULL, -1, 1)) * rdv.primary_quantity * rdv.source_doc_quantity / rdv.total_source_doc_quantity primary_qty, decode(rdv.event_type_id, 15, DECODE(accounting_line_type, 'Receiving Inspection', 1, -1), 16, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 17, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), Decode(rdv.accounting_line_type, 'Landed Cost Absorption', Sign(po_unit_price - unit_landed_cost), 1) * decode(rdv.entered_dr,NULL, -1, 1)) * rdv.primary_quantity raw_qty, rdv.primary_uom uom, round(rdv.po_unit_price * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8) primary_unit_price, round(rdv.prior_unit_price * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8) prior_unit_price, round(rdv.unit_landed_cost * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8) primary_landed_cost, rdv.line_number line_num, rdv.destination_type dest_type, rdv.deliver_to_location deliver_to_location, rdv.currency_code currency_code, NVL(rdv.currency_conversion_rate ,1) curr_conv_rate, (rdv.total_source_doc_quantity/rdv.primary_quantity) po_to_pr_uom_rate, rdv.receipt_number receipt_num, rdv.item_revision rev, rdv.category_id cat, rdv.item_description item_des, NVL(raet.description, rdv.transaction_type) transaction_type, rdv.organization_id organization_id1, rdv.packing_slip packing_slip, round(rdv.source_doc_quantity, :P_qty_precision) source_doc_quantity, rdv.entered_dr entered_dr, rdv.primary_quantity primary_quantity, pov.vendor_name vendor_name, pov.segment1 vendor_number, decode(:P_sort_id,1,rdv.primary_uom) c_uom, decode(:P_sort_id,1,rdv.item_revision) c_rev, decode(:P_sort_id,1,rdv.item_description ) c_des, decode(:P_sort_id,1,msv.concatenated_segments, 2,pov.vendor_name, 3,rdv.receipt_number,1) C_break_column, mp.organization_code to_org, gsob.chart_of_accounts_id coa_id, gsob.currency_code func_currency_code, rdv.accounting_event_id acc_event_id, rdv.event_type_id event_type_id, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_disp', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ACC_DISP, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_FLEX_ACC_DESC, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_DISP, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP, PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )) C_PRIMARY_UNIT_PRICE1, PO_POXRRVDR_XMLP_PKG.c_sort_main_dispformula(fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_sort_main_disp, PO_POXRRVDR_XMLP_PKG.c_cat_dispformula(fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')) C_cat_disp, PO_POXRRVDR_XMLP_PKG.c_vendorformula(pov.vendor_name) C_vendor, PO_POXRRVDR_XMLP_PKG.c_vendor_numberformula(pov.segment1) C_vendor_number, PO_POXRRVDR_XMLP_PKG.display_column(rdv.deliver_to_location,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_item_loc_disp, PO_POXRRVDR_XMLP_PKG.price(rdv.entered_dr,round(rdv.po_unit_price * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8)) C_primary_unit_price, PO_POXRRVDR_XMLP_PKG.calc_trx_value(rdv.currency_code, :C_PRECISION, rdv.event_type_id, PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )), decode(rdv.event_type_id, 15, DECODE(accounting_line_type, 'Receiving Inspection', 1, -1), 16, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 17, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), decode(rdv.entered_dr,NULL, -1, 1)) * rdv.primary_quantity * rdv.source_doc_quantity / rdv.total_source_doc_quantity, NVL(rdv.currency_conversion_rate ,1) , round(rdv.prior_unit_price * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8), round(rdv.unit_landed_cost, 8), rdv.accounting_line_type) C_TRX_VALUE, PO_POXRRVDR_XMLP_PKG.c_quantityformula(rdv.event_type_id,round((decode(rdv.event_type_id, 15, DECODE(accounting_line_type, 'Receiving Inspection', 1, -1), 16, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), 17, DECODE(accounting_line_type, 'Receiving Inspection', -1, 1), Decode(rdv.accounting_line_type, 'Landed Cost Absorption', Sign(po_unit_price - unit_landed_cost), 1) * decode(rdv.entered_dr,NULL, -1, 1)) * rdv.primary_quantity) * rdv.source_doc_quantity / rdv.total_source_doc_quantity, :P_qty_precision)) C_QUANTITY, PO_POXRRVDR_XMLP_PKG.c_primary_unit_price2formula(rdv.event_type_id, PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )), round(rdv.unit_landed_cost , 8), round(rdv.prior_unit_price * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8), rdv.accounting_line_type, NVL(rdv.currency_conversion_rate ,1)) C_PRIMARY_UNIT_PRICE2, rdv.accounting_line_type FROM mtl_system_items_kfv msv , rcv_receiving_acct_distr_v rdv , mtl_item_categories mic , mtl_system_items msi , gl_code_combinations gcc , mtl_categories mca , po_vendors pov , mtl_parameters mp , gl_sets_of_books gsob , rcv_accounting_event_types raet WHERE msi.inventory_item_id = rdv.item_id AND rdv.actual_flag = 'A' AND msi.organization_id = :organization_id AND rdv.item_id is not null AND mic.organization_id = :organization_id AND mic.inventory_item_id = rdv.item_id AND mic.category_set_id = nvl(:P_cat_set_id,mic.category_set_id) AND mic.category_id = mca.category_id AND msv.inventory_item_id = rdv.item_id AND nvl(msv.organization_id,:organization_id) = :organization_id AND gcc.code_combination_id = rdv.code_combination_id AND &P_WHERE_CAT AND &P_WHERE_ITEM AND &P_WHERE_ACC AND &P_WHERE_ACCOUNTING_DATE_FROM AND &P_WHERE_ACCOUNTING_DATE_TO AND &P_where_trx_date_from AND &P_Where_trx_date_to AND pov.vendor_id = rdv.vendor_id AND rdv.organization_id = nvl(:P_org_id,rdv.organization_id) AND rdv.organization_id = mp.organization_id AND &P_WHERE_VENDOR AND &P_WHERE_RECEIPT_NUM_FROM AND &P_WHERE_RECEIPT_NUM_TO AND rdv.set_of_books_id = gsob.set_of_books_id AND rdv.event_type_id = raet.event_type_id (+) UNION ALL SELECT 1 C_dummy_break, rdv.po_number po_number, rdv.code_combination_id ccid, NULL C_FLEX_ITEM, null C_FLEX_ACC, null C_FLEX_CAT, rdv.item_id item_id, rdv.transaction_id trx_id, rdv.accounting_date acct_date, round(decode(rdv.entered_dr,NULL,rdv.primary_quantity* -1,rdv.primary_quantity) * rdv.source_doc_quantity / rdv.total_source_doc_quantity,:P_qty_precision) qty, decode(rdv.entered_dr,NULL,rdv.primary_quantity* -1,rdv.primary_quantity) * rdv.source_doc_quantity / rdv.total_source_doc_quantity primary_qty, decode(rdv.entered_dr,NULL, rdv.primary_quantity* -1, rdv.primary_quantity) raw_qty, rdv.primary_uom uom, round(rdv.po_unit_price * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8) primary_unit_price, round(rdv.prior_unit_price * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8) prior_unit_price, NULL primary_landed_cost, rdv.line_number line_num, rdv.destination_type dest_type, rdv.deliver_to_location deliver_to_location, rdv.currency_code currency_code, NVL(rdv.currency_conversion_rate,1) curr_conv_rate, (rdv.total_source_doc_quantity/rdv.primary_quantity) po_to_pr_uom_rate, rdv.receipt_number receipt_num, rdv.item_revision rev, rdv.category_id cat, rdv.item_description item_des, rdv.transaction_type transaction_type, rdv.organization_id organization_id1, rdv.packing_slip packing_slip, round(rdv.source_doc_quantity, :P_qty_precision) source_doc_quantity, rdv.entered_dr entered_dr, rdv.primary_quantity primary_quantity, pov.vendor_name vendor_name, pov.segment1 vendor_number, decode(:P_sort_id,1,rdv.primary_uom) c_uom, decode(:P_sort_id,1,rdv.item_revision) c_rev, decode(:P_sort_id,1,rdv.item_description ) c_des, decode(:P_sort_id,1,NULL, 2,pov.vendor_name, 3,rdv.receipt_number,1) C_break_column, mp.organization_code to_org, gsob.chart_of_accounts_id coa_id, gsob.currency_code func_currency_code, rdv.accounting_event_id acc_event_id, rdv.event_type_id event_type_id, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_disp', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ACC_DISP, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_FLEX_ACC_DESC, NULL C_FLEX_ITEM_DISP, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP, PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )) C_PRIMARY_UNIT_PRICE1, PO_POXRRVDR_XMLP_PKG.c_sort_main_dispformula(NULL) C_sort_main_disp, PO_POXRRVDR_XMLP_PKG.c_cat_dispformula(fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')) C_cat_disp, PO_POXRRVDR_XMLP_PKG.c_vendorformula(pov.vendor_name) C_vendor, PO_POXRRVDR_XMLP_PKG.c_vendor_numberformula(pov.segment1) C_vendor_number, PO_POXRRVDR_XMLP_PKG.display_column(rdv.deliver_to_location, NULL) C_item_loc_disp, PO_POXRRVDR_XMLP_PKG.price(rdv.entered_dr,round(rdv.po_unit_price * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8)) C_primary_unit_price, PO_POXRRVDR_XMLP_PKG.calc_trx_value(rdv.currency_code , :C_PRECISION, rdv.event_type_id, PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )), decode(rdv.entered_dr,NULL,rdv.primary_quantity* -1,rdv.primary_quantity) * rdv.source_doc_quantity / rdv.total_source_doc_quantity,NVL(rdv.currency_conversion_rate,1),round(rdv.prior_unit_price * (rdv.total_source_doc_quantity/rdv.primary_quantity), 8), NULL, NULL) C_TRX_VALUE, PO_POXRRVDR_XMLP_PKG.c_quantityformula(rdv.event_type_id, round(decode(rdv.entered_dr,NULL,rdv.primary_quantity* -1,rdv.primary_quantity) * rdv.source_doc_quantity / rdv.total_source_doc_quantity,:P_qty_precision)) C_QUANTITY, PO_POXRRVDR_XMLP_PKG.c_primary_unit_price2formula(rdv.accounting_event_id, PO_POXRRVDR_XMLP_PKG.c_primary_unit_price1formula(rdv.accounting_event_id, rdv.transaction_id, round ( rdv.po_unit_price * ( rdv.total_source_doc_quantity / rdv.primary_quantity ) , 8 ), ( rdv.total_source_doc_quantity / rdv.primary_quantity )) , NULL, NULL, NULL, NVL(rdv.currency_conversion_rate,1) ) C_PRIMARY_UNIT_PRICE2 ,rdv.accounting_line_type FROM rcv_receiving_acct_distr_v rdv , gl_code_combinations gcc , mtl_categories mca , po_vendors pov , mtl_parameters mp , gl_sets_of_books gsob WHERE rdv.item_id is Null AND rdv.actual_flag = 'A' AND :P_ITEM_FROM is NULL AND :P_ITEM_TO is NULL AND gcc.code_combination_id = rdv.code_combination_id AND mca.category_id = rdv.category_id AND exists (select category_set_id from mtl_default_sets_view where functional_area_id = 2 AND category_set_id = :P_cat_set_id) AND &P_WHERE_CAT AND &P_WHERE_ACC AND pov.vendor_id = rdv.vendor_id AND rdv.organization_id = nvl(:P_org_id,rdv.organization_id) AND rdv.organization_id = mp.organization_id AND &P_WHERE_ACCOUNTING_DATE_FROM AND &P_WHERE_ACCOUNTING_DATE_TO AND &P_where_trx_date_from AND &P_where_trx_date_to AND &p_where_vendor AND &P_WHERE_RECEIPT_NUM_FROM AND &P_WHERE_RECEIPT_NUM_TO AND gsob.set_of_books_id = rdv.set_of_books_id order by C_dummy_break , C_FLEX_ACC_DISP , coa_id , func_currency_code , C_break_column , primary_qty , prior_unit_price , event_type_id , acc_event_id , po_number , curr_conv_rate , C_FLEX_ITEM_DISP , receipt_num , acct_date , trx_id , primary_quantity , entered_dr , qty , raw_qty , deliver_to_location , currency_code , line_num , transaction_type , dest_type |
Parameters 18
| Parameter | Type | List of values SQL |
|---|---|---|
| Operating Unit | LOV | |
| Title | Char | |
| Sort Option | LOV Oracle | |
| Organization Name | LOV Oracle | |
| Date From | Date | |
| Date To | Date | |
| Item From | Char | |
| Item to | Char | |
| Category Set | LOV Oracle | |
| Category From | Char | |
| Category To | Char | |
| Vendor From | LOV Oracle | |
| Vendor To | LOV Oracle | |
| Receipt Number From | LOV Oracle | |
| Receipt Number To | LOV Oracle | |
| Account From | Char | |
| Account To | Char | |
| Dynamic Precision Option | LOV Oracle |