SELECT C.ORGANIZATION_ID CG_ORG_ID,
CCG.COST_GROUP CG_COST_GROUP,
CCG.DESCRIPTION CG_CG_DESC,
fnd_flex_xml_publisher_apis.process_kff_combination_1('item_seg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CG_ITEM_NUMBER,
MSI.DESCRIPTION CG_ITEM_DESC,
MSI.PRIMARY_UOM_CODE CG_UOM,
null CG_MTL_ACCT,
null CG_MOH_ACCT,
null CG_RES_ACCT,
null CG_OSP_ACCT,
null CG_OVH_ACCT,
null CG_ADJ_ACCT,
ROUND(C.ADJUSTMENT_QUANTITY, :P_qty_precision) CG_ADJ_QUANTITY,
C.COST_ELEMENT_ID CG_COST_ELEMENT_ID,
SUBSTR(CE.COST_ELEMENT,1,15) CG_COST_ELEMENT,
ROUND(C.OLD_UNIT_COST, :EXT_PREC) CG_OLD_UNIT_COST,
ROUND(C.NEW_UNIT_COST, :EXT_PREC) CG_NEW_UNIT_COST,
(ROUND(C.OLD_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT) *
:ROUND_UNIT) CG_OLD_EXT_COST,
(ROUND(C.NEW_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT) *
:ROUND_UNIT) CG_NEW_EXT_COST,
(ROUND(C.NEW_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT) -
ROUND(C.OLD_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT)) *
:ROUND_UNIT CG_ADJUSTMENT,
DECODE(C.COST_ELEMENT_ID,
1, (ROUND(C.NEW_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT)-
ROUND(C.OLD_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT))*
:ROUND_UNIT
,0) CG_MAT_ADJ,
DECODE(C.COST_ELEMENT_ID,
2, (ROUND(C.NEW_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT)-
ROUND(C.OLD_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT))*
:ROUND_UNIT
,0) CG_MOH_ADJ,
DECODE(C.COST_ELEMENT_ID,
3, (ROUND(C.NEW_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT)-
ROUND(C.OLD_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT))*
:ROUND_UNIT
,0) CG_RES_ADJ,
DECODE(C.COST_ELEMENT_ID,
4, (ROUND(C.NEW_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT)-
ROUND(C.OLD_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT))*
:ROUND_UNIT
,0) CG_OSP_ADJ,
DECODE(C.COST_ELEMENT_ID,
5, (ROUND(C.NEW_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT)-
ROUND(C.OLD_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT))*
:ROUND_UNIT
,0) CG_OVH_ADJ,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_mtl_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_MTL_ACCT_SEG,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_moh_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_MOH_ACCT_SEG,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_res_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_RES_ACCT_SEG,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_osp_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_OSP_ACCT_SEG,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_ovh_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_OVH_ACCT_SEG,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_adj_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_ADJ_ACCT_SEG,
BOM_CSTRUSIW_XMLP_PKG.cg_cg_adjformula(:CG_CG_MTL, :CG_CG_MOH, :CG_CG_RES, :CG_CG_OSP, :CG_CG_OVH) CG_CG_ADJ,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') CG_ITEM_PSEG,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_seg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CG_ITEM_SEG
FROM MTL_SYSTEM_ITEMS_VL MSI,
GL_CODE_COMBINATIONS CC1,
GL_CODE_COMBINATIONS CC2,
GL_CODE_COMBINATIONS CC3,
GL_CODE_COMBINATIONS CC4,
GL_CODE_COMBINATIONS CC5,
GL_CODE_COMBINATIONS CC6,
CST_COST_ELEMENTS CE,
CST_COST_GROUP_ACCOUNTS CGA,
MTL_PARAMETERS MP,
CST_COST_GROUPS CCG,
ORG_ORGANIZATION_DEFINITIONS OOD,
CST_STD_COST_ADJ_VALUES C
WHERE C.COST_UPDATE_ID = :P_UPDATE_ID
AND OOD.ORGANIZATION_ID = C.ORGANIZATION_ID
AND C.OLD_UNIT_COST != C.NEW_UNIT_COST
AND CE.COST_ELEMENT_ID = C.COST_ELEMENT_ID
AND C.COST_ELEMENT_ID > 0
AND C.TRANSACTION_TYPE = 1
AND C.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND C.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND C.COST_GROUP_ID = CCG.COST_GROUP_ID
AND C.ORGANIZATION_ID = CGA.ORGANIZATION_ID
AND C.COST_GROUP_ID = CGA.COST_GROUP_ID
AND C.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND CC1.CODE_COMBINATION_ID = DECODE(CCG.COST_GROUP_ID, 1, MP.MATERIAL_ACCOUNT, CGA.MATERIAL_ACCOUNT)
AND CC2.CODE_COMBINATION_ID = DECODE(CCG.COST_GROUP_ID, 1, MP.MATERIAL_OVERHEAD_ACCOUNT, CGA.MATERIAL_OVERHEAD_ACCOUNT)
AND CC3.CODE_COMBINATION_ID = DECODE(CCG.COST_GROUP_ID, 1, MP.RESOURCE_ACCOUNT, CGA.RESOURCE_ACCOUNT)
AND CC4.CODE_COMBINATION_ID = DECODE(CCG.COST_GROUP_ID, 1, MP.OUTSIDE_PROCESSING_ACCOUNT, CGA.OUTSIDE_PROCESSING_ACCOUNT)
AND CC5.CODE_COMBINATION_ID = DECODE(CCG.COST_GROUP_ID, 1, MP.OVERHEAD_ACCOUNT, CGA.OVERHEAD_ACCOUNT)
AND CC6.CODE_COMBINATION_ID = NVL(:P_ADJ_ACCOUNT, DECODE(CCG.COST_GROUP_ID, 1, MP.MATERIAL_ACCOUNT, CGA.MATERIAL_ACCOUNT))
and C.ORGANIZATION_ID=:ORGANIZATION_ID |