CST Simulate Transfer Shop Floor Invoice Variance

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Simulate Transfer Shop Floor Invoice Variance (XML)
Short Name: CSTSIVTW_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
Actual/Simulate
 
LOV Oracle
Validate Adjustment Account
 
Project_Dummy
 
Number
Category Structure
 
Number
Category Validate Flag
 
CST_SRS_COPY_CAT_DUMMY
 
Number
Item_Dummy
 
Number
CST_SRS_IPV_WIP_ITEM_TYPE_DUMMY
 
Number
Organization_Id
 
Number
Invoice Cutoff Date
 
DateTime
Adjustment Account
 
Specific Project
 
LOV Oracle
Specific Category
 
Category Set
 
LOV Oracle
Specific Item
 
Item Range
 
LOV Oracle
IPV Item Type
 
LOV Oracle
Specific Work Order
 
LOV Oracle
Transfer Description