CST Simulate Transfer Shop Floor Invoice Variance

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Simulate Transfer Shop Floor Invoice Variance
Application: Bills of Material
Source: Simulate Transfer Shop Floor Invoice Variance (XML)
Short Name: CSTSIVTW_XML
DB package: BOM_CSTRIVTW_XMLP_PKG
Run CST Simulate Transfer Shop Floor Invoice Variance and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Transfer Description
 
Char
Specific Work Order
 
LOV Oracle
IPV Item Type
 
LOV Oracle
Item Range
 
LOV Oracle
Specific Item
 
Char
Category Set
 
LOV Oracle
Specific Category
 
Char
Specific Project
 
LOV Oracle
Adjustment Account
 
Char
Invoice Cutoff Date
 
DateTime
Blitz Report™