Reports2017-11-18T12:27:27+00:00

CST Shop Floor Invoice Variance

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Shop Floor Invoice Variance Report (XML)
Short Name: CSTRIVTW_XML
DB package: BOM_CSTRIVTW_XMLP_PKG
SELECT     
	 cavh.batch_id,
                 we.wip_entity_id,
                 we.wip_entity_name,
                 cavh.inventory_item_id,          
                 null item_name,
                 pla.item_description,
                 pha.segment1 po_number,
                 pla.line_num po_line_num,
                 pda.distribution_num po_distribution_num,
                 null po_dist_account,
                 cavh.variance_header_id source_line,
                 null adjustment_account,
                 ROUND(cavh.var_amount*1/:ROUND_UNIT)* :ROUND_UNIT header_var_amount,
                 sum(decode(wta.cost_element_id,5,0,nvl(wta.base_transaction_value,0))) applied_value,
                 sum(decode(wta.cost_element_id,5,0,nvl(wta.primary_quantity,0))) applied_quantity,
                 aia.invoice_num,
                 cavl.distribution_line_number,
                 ROUND(cavl.var_amount*1/:ROUND_UNIT)* :ROUND_UNIT line_var_amount,
                 cavh.transaction_date,
				 to_char(cavh.transaction_date,'DD-MON-YY') dsp_transaction_date,
                 ppa.segment1 project_number, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('adjustment_account_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') adjustment_account_seg, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_SEGMENT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') ITEM_PSEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('po_dist_account_seg', 'SQLGL', 'GL#', CC2.CHART_OF_ACCOUNTS_ID, NULL, CC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') po_dist_account_seg
FROM        cst_ap_variance_headers cavh,
                 cst_ap_variance_lines cavl,
                 cst_ap_variance_batches cavb,
                 po_distributions_all pda,
                 po_line_locations_all plla,
                 po_lines_all pla,
                 po_headers_all pha,
                 pa_projects_all ppa,
                 ap_invoices_all aia,
                 mtl_system_items msi,
                 gl_code_combinations cc1,
                 gl_code_combinations cc2,
                 rcv_transactions rt,
                 wip_transactions wt,
                 wip_transaction_accounts wta,
                 wip_entities we
WHERE      cavb.batch_id = nvl(:lp_batch_id, -1)
AND           cavh.batch_id = cavb.batch_id
AND           cavl.variance_header_id = cavh.variance_header_id
AND           pda.po_distribution_id = cavh.po_distribution_id
AND           plla.line_location_id = pda.line_location_id
AND           pla.po_line_id = plla.po_line_id
AND           pha.po_header_id = plla.po_header_id
AND           ppa.project_id (+) = cavl.project_id
AND           aia.invoice_id = cavl.invoice_id
AND           msi.organization_id (+) = :p_organization_id1
AND           msi.inventory_item_id (+) = cavh.inventory_item_id
AND           cc1.code_combination_id = cavb.adjustment_account
AND           cc2.code_combination_id = pda.accrual_account_id
AND           rt.po_distribution_id (+) = cavh.po_distribution_id
AND           wt.rcv_transaction_id (+) = rt.transaction_id
AND           wta.transaction_id (+) = wt.transaction_id
AND           wta.accounting_line_type (+) = 7
AND           we.wip_entity_id = pda.wip_entity_id
GROUP BY cavh.batch_id,
                  we.wip_entity_id,
                  we.wip_entity_name,
                  cavh.inventory_item_id,          
                  &P_ITEM_SEG,
                  pla.item_description,
                  pha.segment1,
                  pla.line_num,
                  pda.distribution_num,
                  &P_ACCT_SEG2,
                  cavh.variance_header_id,
                  &P_ACCT_SEG1,
                  cavh.var_amount,
                  aia.invoice_num,
                  cavl.distribution_line_number,
                  cavl.var_amount,
                  cavh.transaction_date,
                  ppa.segment1,
				  fnd_flex_xml_publisher_apis.process_kff_combination_1('adjustment_account_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('po_dist_account_seg', 'SQLGL', 'GL#', CC2.CHART_OF_ACCOUNTS_ID, NULL, CC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')
Parameter Name SQL text Validation
View Cost Privilege
 
Number
Chart of Accounts Id
 
Number
Print Report
 
LOV Oracle
Transfer IPV
 
LOV Oracle
Organization_Id
 
Number
Batch Id
 
LOV Oracle
Batch_Date
 
Date
By continuing to use the site, you agree to the use of cookies. Accept