[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE pm_insert_pti_pvt
(p_transaction_source VARCHAR2,
p_batch_name VARCHAR2,
p_expenditure_ending_date DATE,
p_employee_number VARCHAR2,
p_organization_name VARCHAR2,
p_expenditure_item_date DATE,
p_project_number VARCHAR2,
p_task_number VARCHAR2,
p_expenditure_type VARCHAR2,
p_pa_quantity NUMBER,
p_raw_cost NUMBER,
p_expenditure_comment VARCHAR2,
p_orig_transaction_reference VARCHAR2,
p_raw_cost_rate NUMBER,
p_unmatched_negative_txn_flag VARCHAR2,
p_gl_date DATE,
p_org_id NUMBER,
p_burdened_cost NUMBER,
p_burdened_cost_rate NUMBER,
p_system_linkage VARCHAR2,
p_transaction_status_code VARCHAR2,
p_denom_currency_code VARCHAR2,
p_transaction_id NUMBER,
p_transaction_action_id NUMBER,
p_transaction_source_type_id NUMBER,
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_cost_element_id NUMBER,
p_resource_id NUMBER,
p_source_flag NUMBER,
p_variance_flag NUMBER,
p_primary_quantity NUMBER,
p_transfer_organization_id NUMBER,
p_fob_point NUMBER,
p_wip_entity_id NUMBER,
p_basis_resource NUMBER,
p_type_class NUMBER,
p_project_id NUMBER,
p_task_id NUMBER,
p_transaction_date DATE,
p_cost_group_id NUMBER,
p_transfer_cost_group_id NUMBER,
p_transaction_source_id NUMBER,
p_to_project_id NUMBER,
p_to_task_id NUMBER,
p_source_project_id NUMBER,
p_source_task_id NUMBER,
p_transfer_transaction_id NUMBER,
p_primary_cost_method NUMBER,
p_acct_period_id NUMBER,
p_exp_org_id NUMBER,
p_distribution_account_id NUMBER,
p_proj_job_ind NUMBER,
p_first_matl_se_exp_type VARCHAR2,
p_inv_txn_source_literal VARCHAR2,
p_cap_txn_source_literal VARCHAR2,
p_inv_syslink_literal VARCHAR2,
p_bur_syslink_literal VARCHAR2,
p_wip_syslink_literal VARCHAR2,
p_user_def_exp_type VARCHAR2,
p_flow_schedule VARCHAR2,
p_si_asset_yes_no NUMBER,
p_transfer_si_asset_yes_no NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2
);
| For MMT trasactions, all transactions that are not selected by the view |
| 'cst_pm_matl_txn_v' and satisfy the date and org criteria are marked as |
| cost collected with no exception. |
| |
| For WIP trasactions, transactions falling into either of these category |
| are marked as cost collected. |
| -- All wip transaction records that refer to a non-project costed job |
| -- All wt records with transaction type as other than (1,2,3) and refer |
| to a project costed job |
| -- All wt records with transaction type as (1,2,3) but have no records |
| in WTA because their standard_rate_flag was 2 and auto_charge_type as|
| any thing other than manual resulting in the resource having no cost.|
| -- All ipv transfer transactions (source_code = 'IPV' |
| |
| PARAMETERS |
| Organization_Id, |
| UpToDate, |
| p_user_id, |
| p_login_id, |
| p_req_id, |
| p_prg_appl_id, |
| p_prg_id, |
| O_err_num, |
| O_err_code, |
| O_err_msg */
PROCEDURE pm_mark_non_project_world_txns (
p_Org_Id NUMBER,
p_prior_days NUMBER,
p_user_id NUMBER,
p_login_id NUMBER,
p_req_id NUMBER,
p_prg_appl_id NUMBER,
p_prg_id NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2)
IS
l_err_num NUMBER;
SELECT mp.primary_cost_method
INTO l_primary_cost_method
FROM mtl_parameters mp
WHERE mp.organization_id = p_org_id;
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE NOT EXISTS
( SELECT NULL
FROM mtl_transaction_types mtt
WHERE mtt.type_class = 1
AND mtt.transaction_type_id = mmt.transaction_type_id )
AND mmt.organization_id = p_Org_Id
AND mmt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND mmt.costed_flag is NULL
AND mmt.pm_cost_collected = 'N'
AND mmt.transaction_action_id <> 17; -- See Note about exception above
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE NOT EXISTS
( SELECT NULL
FROM cst_pm_matl_txn_v cpmtv
WHERE cpmtv.transaction_id = mmt.transaction_id )
AND mmt.organization_id = p_Org_Id
AND mmt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND mmt.costed_flag is NULL
AND mmt.pm_cost_collected = 'N';
UPDATE
mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE
(
mmt.organization_id <> nvl(mmt.owning_organization_id, mmt.organization_id)
OR nvl(mmt.owning_tp_type, 2) <> 2
)
AND mmt.pm_cost_collected = 'N'
AND mmt.organization_id = p_Org_Id
AND mmt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND mmt.costed_flag is null;
/* cost collected. Retroactive price updates will laso be set as cost */
/* collected */
UPDATE
mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.pm_cost_collected = 'N'
AND mmt.organization_id = p_org_id
AND mmt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND mmt.costed_flag is null
AND
(
(
(
NVL(mmt.logical_transaction,2) = 1
)
AND NOT ( MMT.TRANSACTION_TYPE_ID = 19
AND MMT.TRANSACTION_ACTION_ID = 26
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 1
AND NVL(MMT.LOGICAL_TRX_TYPE_CODE,5) = 2
AND EXISTS
(
SELECT
1
FROM rcv_transactions rcv
WHERE rcv.transaction_id = NVL(mmt.rcv_transaction_id,-9999)
AND rcv.organization_id = p_org_id
)
)
)
OR NVL(mmt.logical_trx_type_code,5) = 4
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEBUG: Count of logical txns or retroactive price updates in MMT - no cost collection - is #: '||to_char(SQL%ROWCOUNT) );
UPDATE
wip_transactions wt
SET wt.pm_cost_collected = NULL,
wt.last_update_date = sysdate,
wt.last_updated_by = p_user_id,
wt.last_update_login = p_login_id,
wt.request_id = p_req_id,
wt.program_application_id = p_prg_appl_id,
wt.program_id = p_prg_id,
wt.program_update_date = sysdate
WHERE wt.organization_id = p_Org_Id
AND wt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND wt.pm_cost_collected = 'N'
AND
(
(
NOT wt.transaction_type in (1,2,3,17)
AND wt.project_id IS NOT NULL
)
OR wt.project_id IS NULL
OR
(
wt.transaction_type in (1,2,3)
AND NOT EXISTS
(
SELECT
NULL
FROM wip_transaction_accounts wta
WHERE wta.transaction_id = wt.transaction_id
)
)
OR wt.source_code = 'IPV' -- Bug 2130771
);
| This procedure would for every record selected from the view, update the|
| MMT record to assign a group_id. The number of records to be updated is |
| determined by the user_spec_group_size. |
| |
| Every Transaction that satisfies the conditions mentioned below gets |
| a group id assigned to itself. |
| |
| - All Project related transactions from the Project World |
| |
| - All Project related transactions from the Non-Proj World |
| - Select Txns: Capital Projects related txns in the NPW |
| - Select Txns: Component Issue to Project Job from a NPW |
| - Select Txns: Component Return from Project Job to NPW |
| |
| - Ensure that the transaction has not yet got any group assigned |
| |
| - Ensure that the transaction took place before the Date upto which the |
| Cost Collection was desired |
| |
| - Ensure that the transaction took place in the Org for which the cost |
| Cost Collection was desired |
| |
| PARAMETERS |
| p_Org_Id, |
| p_prior_days, |
| p_user_spec_group_size, |
| p_rows_processed, |
| p_group_id OUT, |
| p_user_id, |
| p_login_id, |
| p_req_id, |
| p_prg_appl_id, |
| p_prg_id, |
| p_proj_misc_txn_only, |
| O_err_num, |
| O_err_code, |
| O_err_msg |
| |
| HISTORY |
| 07-SEP-96 Bhaskar Dasari Created. |
*----------------------------------------------------------------------------*/
PROCEDURE assign_groups_to_mmt_txns ( p_Org_Id NUMBER,
p_prior_days NUMBER,
p_user_spec_group_size NUMBER,
p_rows_processed OUT NOCOPY NUMBER,
p_group_id OUT NOCOPY NUMBER,
p_user_id NUMBER,
p_login_id NUMBER,
p_req_id NUMBER,
p_prg_appl_id NUMBER,
p_prg_id NUMBER,
p_proj_misc_txn_only NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2)
IS
CURSOR sel_mmt_trx (c_Org_Id NUMBER,
c_prior_days NUMBER,
c_proj_misc_txn_only NUMBER,
c_user_spec_group_size NUMBER) IS
SELECT NULL
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id in (
SELECT cpmtv.transaction_id
FROM cst_pm_matl_txn_v cpmtv
WHERE cpmtv.organization_id = c_Org_Id
AND cpmtv.transaction_date <=
((trunc(sysdate) - c_prior_days) + 0.99999)
AND cpmtv.pm_cost_collector_group_id is NULL
AND rownum <= c_user_spec_group_size
AND ( cpmtv.type_class = decode(c_proj_misc_txn_only,1,1,cpmtv.type_class)
OR cpmtv.transaction_action_id=17)
)
FOR UPDATE OF mmt.pm_cost_collected NOWAIT;
SELECT mtl_material_transactions_s.nextval
INTO p_group_id
FROM dual;
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collector_group_id = p_group_id,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE current of sel_mmt_trx;
SELECT cpmtv.transaction_action_id,
cpmtv.transaction_source_type_id,
cpmtv.type_class,
cpmtv.expenditure_type,
cpmtv.transaction_date,
cpmtv.project_id,
cpmtv.task_id,
cpmtv.inventory_item_id,
cpmtv.primary_quantity,
cpmtv.costed_flag,
cpmtv.primary_cost_method,
cpmtv.avg_rates_cost_type_id,
cpmtv.item_description,
cpmtv.cost_group_id,
cpmtv.transfer_cost_group_id,
cpmtv.transaction_source_id,
cpmtv.to_project_id,
cpmtv.to_task_id,
cpmtv.source_project_id,
cpmtv.source_task_id,
cpmtv.transfer_transaction_id,
cpmtv.acct_period_id,
cpmtv.pm_cost_collector_group_id,
cpmtv.exp_org_id,
cpmtv.distribution_account_id,
cpmtv.transfer_organization_id,
cpmtv.flow_schedule,
cpmtv.si_asset_yes_no,
cpmtv.transfer_si_asset_yes_no
FROM cst_pm_matl_txn_v cpmtv
WHERE cpmtv.transaction_id = c_Transaction_Id
AND cpmtv.organization_id = c_Organization_Id;
SELECT count(*)
INTO l_count
FROM cst_pm_matl_txn_v
WHERE transaction_id = p_transaction_id;
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.pm_cost_collector_group_id = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE
mmt.transaction_id =p_transaction_id;
/* Bug 5241396.Need to update the pm_cost_collected to NULL only for direct interorg and sub
inventory txfrs and not for intransit receipts that will have a txnfr txn ID */
/* Bug #2623627. Updating group_id for the transfer transaction_id so
the transfer to projects field does not show "Not Applicable" */
If cpmtv_rec.transaction_action_id <> 12 then
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.pm_cost_collector_group_id = cpmtv_rec.pm_cost_collector_group_id,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.transaction_id IN (p_transaction_id, cpmtv_rec.transfer_transaction_id);
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.pm_cost_collector_group_id = cpmtv_rec.pm_cost_collector_group_id,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.transaction_id = p_transaction_id;
has not yet been costed but the sending txn is being cost collected. The sending txn is being updated with a warning but
pm_cost_collected flag is not being updated to error to prevent
user from manually resubmitting the txn for cost collection */
rollback to pm_cc_worker_mmt;
UPDATE mtl_material_transactions mmt
SET
mmt.error_explanation = l_err_msg,
mmt.pm_cost_collector_group_id = null,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.transaction_id = p_transaction_id;
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.pm_cost_collector_group_id = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.transaction_id =p_transaction_id;
| This procedure would for every record selected from the view, update the|
| WT record to assign a group_id. The number of records to be updated is |
| determined by the user_spec_group_size. Delete all 'WITE' records if any|
| for the transactions that were assigned the group_id. |
| |
| Every Transaction that satisfies the conditions mentioned below gets |
| a group id assigned to itself. |
| |
| - All transactions resulting from a job that is Project related |
| |
| - Select Txns: Resource, O/P and Overhead |
| |
| - Ensure that the transaction has not yet been assigned a group_id |
| |
| - Ensure that the transaction took place before the Date upto which the |
| Cost Collection was desired |
| |
| - Ensure that the transaction took place in Org for which the Cost Coll |
| was desired |
| |
| PARAMETERS |
| p_Org_Id, |
| p_prior_days, |
| p_user_spec_group_size, |
| p_rows_processed, |
| p_group_id OUT, |
| p_user_id, |
| p_login_id, |
| p_req_id, |
| p_prg_appl_id, |
| p_prg_id, |
| O_err_num, |
| O_err_code, |
| O_err_msg |
| |
| HISTORY |
| 07-SEP-96 Bhaskar Dasari Created. |
| |
| 21-NOV-97 Hemant Gosain Modified. Refer Bug# 589460 Regarding |
| PJM Auto Task API. Get Prj/Tsk Ref from Table WT. |
*----------------------------------------------------------------------------*/
PROCEDURE assign_groups_to_wt_txns (
p_Org_Id NUMBER,
p_prior_days NUMBER,
p_user_spec_group_size NUMBER,
p_rows_processed OUT NOCOPY NUMBER,
p_group_id OUT NOCOPY NUMBER,
p_user_id NUMBER,
p_login_id NUMBER,
p_req_id NUMBER,
p_prg_appl_id NUMBER,
p_prg_id NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2)
IS
CURSOR sel_wt_trx ( c_Org_Id NUMBER,
c_prior_days NUMBER,
c_user_spec_group_size NUMBER) IS
SELECT NULL
FROM wip_transactions wt
WHERE wt.organization_id = c_Org_Id
AND wt.transaction_date <= ((trunc(sysdate) - c_prior_days) + 0.99999)
AND wt.pm_cost_collected = 'N'
AND wt.transaction_type in (1,2,3,17)
AND wt.project_id IS NOT NULL -- Bug #589460
AND wt.pm_cost_collector_group_id is NULL
AND rownum <= c_user_spec_group_size
FOR UPDATE OF wt.pm_cost_collected NOWAIT;
SELECT wip_transactions_S.nextval
INTO l_group_id
FROM dual;
UPDATE wip_transactions wt
SET wt.pm_cost_collector_group_id = l_group_id,
wt.last_update_date = sysdate,
wt.last_updated_by = p_user_id,
wt.last_update_login = p_login_id,
wt.request_id = p_req_id,
wt.program_application_id = p_prg_appl_id,
wt.program_id = p_prg_id,
wt.program_update_date = sysdate
WHERE CURRENT of sel_wt_trx;
DELETE wip_txn_interface_errors wite
WHERE wite.transaction_id in
( SELECT wt.transaction_id
FROM wip_transactions wt
WHERE wt.pm_cost_collector_group_id = l_group_id
AND wt.pm_cost_collected = 'N' )
AND wite.error_column = 'PM_COST_COLLECTED';
| Enhanced Selection criteria to include WTA reference account |
| to support Project Capitalization. |
| |
| 21-NOV-97 Hemant Gosain Modified. |
| Refer Bug# 589460. Get Prj/Tsk form WT Table. |
*----------------------------------------------------------------------------*/
PROCEDURE pm_cc_worker_wt (
p_transaction_id NUMBER,
p_Org_Id NUMBER,
p_wip_txn_source_literal VARCHAR2,
p_wip_straight_time_literal VARCHAR2,
p_wip_syslink_literal VARCHAR2,
p_bur_syslink_literal VARCHAR2,
p_denom_currency_code VARCHAR2,
p_user_id NUMBER,
p_login_id NUMBER,
p_req_id NUMBER,
p_prg_appl_id NUMBER,
p_prg_id NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2)
IS
/* The CURSOR has been changed owing to Bug#589460 and the UNION of WDJ and CFM
has been removed because we pick the prj/task reference from the WT Table
rather than from the Entity Definition.
*/
/* Added DISTINCT clause because ppf could have multiple records based on
effectivity dates for the same person_id Bug # 703956 */
/*Included the business_group_id parameter in the cursor bug 2124765 */
CURSOR sel_wt_trx_to_cost (C_transaction_id NUMBER,
C_organization_id NUMBER) IS
SELECT DISTINCT wta.transaction_date c_transaction_date,
ppf.business_group_id c_business_group_id,
ppf.employee_number c_employee_number,
wta.base_transaction_value c_base_transaction_value,
wt.primary_quantity c_primary_quantity,
wta.resource_id c_resource_id,
br.description c_resource_description,
br.expenditure_type c_expenditure_type,
wt.project_id c_project_id,
wt.task_id c_task_id,
bd.pa_expenditure_org_id c_pa_expenditure_org_id,
wt.acct_period_id c_acct_period_id,
wt.pm_cost_collector_group_id c_group_id,
wt.department_id c_department_id,
wt.transaction_type c_transaction_type,
wta.cost_element_id c_cost_element_id,
wt.wip_entity_id c_wip_entity_id,
wta.accounting_line_type c_accounting_line_type,
wt.primary_uom c_primary_uom,
wta.basis_resource_id c_basis_resource_id,
wta.reference_account c_reference_account,
wta.wip_sub_ledger_id c_wip_dr_sub_ledger_id
FROM wip_transaction_accounts wta,
wip_transactions wt,
bom_resources br,
bom_departments bd,
per_people_f ppf
WHERE wt.transaction_type in (1,2,3)
AND wta.accounting_line_type = 7
AND wt.transaction_id = wta.transaction_id
AND br.resource_id = wta.resource_id
AND bd.department_id = wt.department_id
AND bd.organization_id = wt.organization_id
AND wt.transaction_id = C_transaction_id
AND wt.organization_id = C_organization_id
AND ppf.person_id (+) = wt.employee_id
/* Bug:2395906*/
AND ppf.effective_start_date(+) <= trunc(sysdate)
AND ppf.effective_end_date(+) >= trunc(sysdate)
AND (ppf.employee_number is not null or ppf.person_id is null)
/*Added the above and condition to check if the employee_number
is not null.This is modified for porting bug #1573297 in 11.0
to 11.5 Bug 1660313*/
UNION
SELECT DISTINCT wta.transaction_date c_transaction_date,
to_number(NULL) c_business_group_id,
NULL c_employee_number,
wta.base_transaction_value c_base_transaction_value,
wt.primary_quantity c_primary_quantity,
wta.resource_id c_resource_id,
pla.item_description c_resource_description,
ppp.dir_item_expenditure_type c_expenditure_type,
wt.project_id c_project_id,
wt.task_id c_task_id,
wt.organization_id c_pa_expenditure_org_id,
wt.acct_period_id c_acct_period_id,
wt.pm_cost_collector_group_id c_group_id,
wt.department_id c_department_id,
wt.transaction_type c_transaction_type,
wta.cost_element_id c_cost_element_id,
wt.wip_entity_id c_wip_entity_id,
wta.accounting_line_type c_accounting_line_type,
wt.primary_uom c_primary_uom,
wta.basis_resource_id c_basis_resource_id,
wta.reference_account c_reference_account,
wta.wip_sub_ledger_id c_wip_dr_sub_ledger_id
FROM wip_transaction_accounts wta,
wip_transactions wt,
pjm_project_parameters ppp,
po_lines_all pla
WHERE wt.transaction_type = 17
AND pla.po_line_id = wt.po_line_id
AND wta.accounting_line_type = 7
AND wt.transaction_id = wta.transaction_id
AND ppp.organization_id = wt.organization_id
AND ppp.project_id = wt.project_id
AND wt.transaction_id = C_transaction_id
AND wt.organization_id = C_organization_id;
SELECT decode(wt_rec.c_transaction_type,
17, 5, --Rcv Inspection(Direct Item)
decode(wt_rec.c_cost_element_id,
3, 4, --RES ABSO;RES
SELECT NVL(reference_account,-99)
INTO l_cr_code_combination_id
FROM wip_transaction_accounts
WHERE transaction_id = p_transaction_id
AND organization_id = p_org_id
AND cost_element_id = wt_rec.c_cost_element_id
AND accounting_line_type = l_accounting_line_type
AND NVL(resource_id,-99) = NVL(wt_rec.c_resource_id,-99);
SELECT MAX(WIP_SUB_LEDGER_ID)
INTO l_wip_cr_sub_ledger_id
FROM wip_transaction_accounts wta
WHERE transaction_id = p_transaction_id
AND reference_account = l_cr_code_combination_id
AND organization_id = p_org_id
AND cost_element_id = wt_rec.c_cost_element_id
AND accounting_line_type = l_accounting_line_type
AND NVL(resource_id,-99) = NVL(wt_rec.c_resource_id,-99);
for insertion into PA_TRANSACTION_INTERFACE.Bug Fix for
Bug 2124765 */
if wt_rec.c_business_group_id is not null THEN
Select haout.name into l_business_group_name
From hr_all_organization_units_tl haout
WHERE
haout.organization_id = wt_rec.c_business_group_id
AND haout.language = USERENV('LANG');
UPDATE wip_transactions wt
SET wt.pm_cost_collected = NULL,
wt.last_update_date = sysdate,
wt.last_updated_by = p_user_id,
wt.last_update_login = p_login_id,
wt.request_id = p_req_id,
wt.program_application_id = p_prg_appl_id,
wt.program_id = p_prg_id,
wt.program_update_date = sysdate
WHERE wt.transaction_id = p_transaction_id;
NO_ROWS_TO_INSERT EXCEPTION;
CST_FAILED_INSERT_PTI EXCEPTION;
SELECT p_cap_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_source_project_number project_number,
l_source_task_number task_number,
decode(mcacd.cost_element_id,
2,0,
5,0,
(-1) * p_primary_quantity) quantity,
decode(mcacd.cost_element_id,
2,0,
5,0,
(-1)*sum(p_primary_quantity * mcacd.actual_cost))
raw_cost,
p_item_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(mcacd.cost_element_id,
2,0,
5,0,
sum(mcacd.actual_cost)) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_source_proj_org_id org_id,
(-1)*sum(p_primary_quantity *
mcacd.actual_cost) burdened_cost,
sum(mcacd.actual_cost) burdened_cost_rate,
decode( mcacd.cost_element_id,
2, p_bur_syslink_literal,
3, p_wip_syslink_literal,
4, p_wip_syslink_literal,
5, p_bur_syslink_literal,
p_inv_syslink_literal) system_linkage,
'P' transaction_status_code,
mcacd.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
mtl_material_transactions mmt
WHERE mmt.transaction_id = p_transaction_id
AND mmt.transaction_id = mcacd.transaction_id
AND mcacd.organization_id = p_organization_id
AND mcacd.inventory_item_id = p_inventory_item_id
AND mcacd.actual_cost <> 0
GROUP BY p_transaction_id,
mcacd.cost_element_id;
SELECT p_inv_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_project_number project_number,
l_task_number task_number,
br.EXPENDITURE_TYPE expenditure_type,
0 quantity, /* Qty=0 if l=burden */
0 raw_cost, /* RawCost=0 if l=burden */
br.description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
0 raw_cost_rate, /*RawCostrate=0 if l=burden */
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_proj_org_id org_id,
macs.ACTUAL_COST*p_primary_quantity burdened_cost,
macs.ACTUAL_COST burdened_cost_rate,
p_bur_syslink_literal system_linkage,
'P' transaction_status_code,
br.resource_id resource_id,
macs.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_actual_cost_subelement macs,
bom_resources br,
mtl_parameters mp
WHERE macs.transaction_id = p_transaction_id
AND macs.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.cost_organization_id = br.organization_id
AND macs.cost_element_id = 2
AND macs.level_type = 1
AND macs.RESOURCE_ID = br.RESOURCE_ID
AND macs.ACTUAL_COST <> 0;
SELECT p_inv_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_xfer_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_to_project_number project_number,
l_to_task_number task_number,
br.EXPENDITURE_TYPE expenditure_type,
0 quantity, /* Qty=0 if l=burden */
0 raw_cost, /* RawCost=0 if l=burden */
br.description expenditure_comment,
to_char(p_transfer_transaction_id) orig_transaction_reference,
0 raw_cost_rate, /*RawCostrate=0 if l=burden */
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_to_proj_org_id org_id,
(-1)*macs.ACTUAL_COST*p_primary_quantity burdened_cost,
macs.ACTUAL_COST burdened_cost_rate,
p_bur_syslink_literal system_linkage,
'P' transaction_status_code,
br.resource_id resource_id,
macs.cost_element_id cost_element_id,
l_xfer_currency_code denom_currency_code
FROM mtl_actual_cost_subelement macs,
bom_resources br
WHERE macs.transaction_id = p_transfer_transaction_id
AND macs.ORGANIZATION_ID = br.ORGANIZATION_ID
AND macs.cost_element_id = 2
AND macs.level_type = 1
AND macs.RESOURCE_ID = br.RESOURCE_ID
AND macs.ACTUAL_COST <> 0;
SELECT decode(p_type_class,1,
p_cap_txn_source_literal,
p_inv_txn_source_literal) transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_project_number project_number,
l_task_number task_number,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,p_primary_quantity
)) quantity,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,
sum(mcacd.ACTUAL_COST)*p_primary_quantity
)) raw_cost,
p_item_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,sum(mcacd.ACTUAL_COST)
)) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_proj_org_id org_id,
((sum(mcacd.ACTUAL_COST)*p_primary_quantity) - (NVL((sum(temp.actual_cost)*p_primary_quantity),0))) burdened_cost,
(sum(mcacd.ACTUAL_COST)-(NVL(sum(temp.actual_cost),0))) burdened_cost_rate,
decode(mcacd.cost_element_id,2, p_bur_syslink_literal,
decode(mcacd.cost_element_id,3, p_wip_syslink_literal,
decode(mcacd.cost_element_id,4, p_wip_syslink_literal,
decode(mcacd.cost_element_id,5, p_bur_syslink_literal,
p_inv_syslink_literal)))) system_linkage,
'P' transaction_status_code ,
mcacd.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
(Select SUM(actual_cost) actual_cost,
transaction_id,
organization_id,
cost_element_id,
level_type,
layer_id
from mtl_actual_cost_subelement macs
where transaction_id = p_transaction_id
and organization_id = p_organization_id
group by transaction_id,
organization_id,
cost_element_id,
level_type,
layer_id
) temp,
cst_cg_item_costs_view ccicv --PJMSTD
WHERE mcacd.transaction_id = p_transaction_id
AND mcacd.organization_id = p_organization_id
AND mcacd.inventory_item_id = p_inventory_item_id
AND mcacd.actual_cost <> 0
AND mcacd.layer_id = decode(p_primary_cost_method,
1, -1,ccicv.layer_id) --PJMSTD
AND temp.transaction_id(+) =
mcacd.transaction_id
AND temp.organization_id(+) = mcacd.organization_id
AND temp.cost_element_id(+) = mcacd.cost_element_id
AND temp.level_type(+) = mcacd.level_type
AND ccicv.organization_id = p_organization_id
AND ccicv.inventory_item_id = p_inventory_item_id
AND ccicv.cost_group_id = decode(p_primary_cost_method,
1, 1, p_cost_group_id) --PJMSTD
GROUP BY mcacd.transaction_id,
mcacd.cost_element_id
UNION ALL /* BUG #2972878, 2580132 */
SELECT decode(p_type_class,1,
p_cap_txn_source_literal,
p_inv_txn_source_literal) transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_project_number project_number,
l_task_number task_number,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,p_primary_quantity)) quantity,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,
sum(mcacd.ACTUAL_COST)*p_primary_quantity
))raw_cost,
p_item_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,sum(mcacd.ACTUAL_COST)
)) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_proj_org_id org_id,
((sum(mcacd.ACTUAL_COST)*p_primary_quantity) - (NVL((sum(temp.actual_cost)*p_primary_quantity),0))) burdened_cost,
(sum(mcacd.ACTUAL_COST)-(NVL(sum(temp.actual_cost),0))) burdened_cost_rate,
decode(mcacd.cost_element_id,2, p_bur_syslink_literal,
decode(mcacd.cost_element_id,3, p_wip_syslink_literal,
decode(mcacd.cost_element_id,4, p_wip_syslink_literal,
decode(mcacd.cost_element_id,5, p_bur_syslink_literal,
p_inv_syslink_literal)))) system_linkage,
'P' transaction_status_code ,
mcacd.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
(Select SUM(actual_cost) actual_cost,
transaction_id,
organization_id,
cost_element_id,
level_type,
layer_id
from mtl_actual_cost_subelement macs
where transaction_id = p_transaction_id
and organization_id = p_organization_id
group by transaction_id,
organization_id,
cost_element_id,
level_type,
layer_id
) temp,
mtl_system_items msi
WHERE mcacd.transaction_id = p_transaction_id
AND mcacd.organization_id = p_organization_id
AND mcacd.inventory_item_id = p_inventory_item_id
AND mcacd.inventory_item_id = msi.inventory_item_id
AND mcacd.organization_id = msi.organization_id
AND msi.costing_enabled_flag = 'N'
AND mcacd.actual_cost <> 0
/* AND NOT EXISTS (
SELECT null
FROM mtl_actual_cost_subelement macs
WHERE macs.transaction_id =
mcacd.transaction_id
AND macs.organization_id =
mcacd.organization_id
AND macs.cost_element_id =
mcacd.cost_element_id
AND macs.level_type = mcacd.level_type
)*/
AND temp.transaction_id(+) = mcacd.transaction_id
AND temp.organization_id(+) = mcacd.organization_id
AND temp.cost_element_id(+) = mcacd.cost_element_id
AND temp.level_type(+) = mcacd.level_type
GROUP BY mcacd.transaction_id,
mcacd.cost_element_id;
SELECT p_inv_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
decode(p_transaction_action_id,
3, l_xfer_organization_name,
l_organization_name) organization_name,
p_transaction_date expenditure_item_date,
l_to_project_number project_number,
l_to_task_number task_number,
decode(sign(p_primary_quantity),1, cceet.EXPENDITURE_TYPE_OUT,
cceet.EXPENDITURE_TYPE_IN) expenditure_type,
decode(mcacd.cost_element_id,2,0, decode(mcacd.cost_element_id,5,0,
(-1) * p_primary_quantity)) quantity,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,
(-1) * sum(mcacd.ACTUAL_COST)*p_primary_quantity)) raw_cost,
p_item_description expenditure_comment,
to_char(p_transfer_transaction_id)
orig_transaction_reference,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,sum(mcacd.ACTUAL_COST)))
raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_to_proj_org_id org_id,
(-1)*((sum(mcacd.ACTUAL_COST)*p_primary_quantity)-
(NVL(sum(macs.actual_cost)*p_primary_quantity,0)))
burdened_cost,
(sum(mcacd.ACTUAL_COST)-
(NVL(sum(macs.actual_cost),0))) burdened_cost_rate,
decode(mcacd.cost_element_id,2, p_bur_syslink_literal,
decode(mcacd.cost_element_id,3, p_wip_syslink_literal,
decode(mcacd.cost_element_id,4, p_wip_syslink_literal,
decode(mcacd.cost_element_id,5, p_bur_syslink_literal,
p_inv_syslink_literal)))) system_linkage,
'P' transaction_status_code,
mcacd.cost_element_id cost_element_id,
decode(p_transaction_action_id,
3, l_xfer_currency_code,
p_denom_currency_code) denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
cst_cost_elem_exp_types cceet,
mtl_actual_cost_subelement macs
WHERE mcacd.transaction_id = decode(p_transaction_action_id,
3, p_transfer_transaction_id ,
p_transaction_id)
AND mcacd.organization_id = decode(p_transaction_action_id,
3, p_transfer_organization_id,
p_organization_id)
AND cceet.cost_element_id = mcacd.cost_element_id
AND mcacd.actual_cost <> 0
AND ((l_primary_cost_method_snd = 1)
OR
EXISTS
( SELECT 'X' from cst_quantity_layers cql
where mcacd.layer_id = cql.layer_id
AND cql.organization_id = mcacd.organization_id
AND cql.inventory_item_id = p_inventory_item_id
AND cql.cost_group_id = nvl(p_transfer_cost_group_id,1)
)
)
AND macs.transaction_id(+) = mcacd.transaction_id
AND macs.organization_id(+) = mcacd.organization_id
AND macs.cost_element_id(+) = mcacd.cost_element_id
AND macs.level_type(+) = mcacd.level_type
GROUP BY mcacd.cost_element_id,
cceet.expenditure_type_in,
cceet.expenditure_type_out;
SELECT decode(p_type_class,1,p_cap_txn_source_literal,
p_inv_txn_source_literal) transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_source_project_number project_number,
l_source_task_number task_number,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,(-1) * p_primary_quantity)) quantity,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,
(-1) * sum(mcacd.ACTUAL_COST) * p_primary_quantity)) raw_cost,
p_item_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,sum(mcacd.ACTUAL_COST))) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_source_proj_org_id org_id,
(-1) * sum(mcacd.ACTUAL_COST)*p_primary_quantity burdened_cost,
sum(mcacd.ACTUAL_COST) burdened_cost_rate,
decode(mcacd.cost_element_id,2, p_bur_syslink_literal,
decode(mcacd.cost_element_id,3, p_wip_syslink_literal,
decode(mcacd.cost_element_id,4, p_wip_syslink_literal,
decode(mcacd.cost_element_id,5, p_bur_syslink_literal,
p_inv_syslink_literal)))) system_linkage,
'P' transaction_status_code,
mcacd.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
cst_cg_item_costs_view ccicv
WHERE mcacd.transaction_id = p_transaction_id
AND mcacd.organization_id = p_organization_id
AND mcacd.inventory_item_id = p_inventory_item_id
AND mcacd.actual_cost <> 0
AND mcacd.layer_id = decode(p_primary_cost_method,
1, -1, ccicv.layer_id) --PJMSTD
AND NOT EXISTS (
SELECT null
FROM mtl_actual_cost_subelement macs
WHERE macs.transaction_id =
mcacd.transaction_id
AND macs.organization_id =
mcacd.organization_id
AND macs.cost_element_id =
mcacd.cost_element_id
AND macs.level_type = mcacd.level_type )
AND ccicv.organization_id = p_organization_id
AND ccicv.inventory_item_id = p_inventory_item_id
AND ccicv.cost_group_id = decode(p_primary_cost_method,
1,1, p_cost_group_id) --PJMSTD
GROUP BY mcacd.transaction_id,
mcacd.cost_element_id;
SELECT p_inv_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_project_number project_number,
l_task_number task_number,
decode(sign(p_primary_quantity),1, cceet.EXPENDITURE_TYPE_OUT,
cceet.EXPENDITURE_TYPE_IN) expenditure_type,
-- bug 923134
decode(mcacd.cost_element_id,
2,0,
5,0,p_primary_quantity) quantity,
decode(mcacd.cost_element_id,
2,0,
5,0,
sum(mcacd.payback_variance_amount)* abs(p_primary_quantity))
raw_cost,
p_item_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(mcacd.cost_element_id,
2,0,
5,0,
sum(mcacd.payback_variance_amount)) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_proj_org_id org_id,
sum(mcacd.payback_variance_amount)* abs(p_primary_quantity)
burdened_cost,
sum(mcacd.payback_variance_amount) burdened_cost_rate,
decode(mcacd.cost_element_id,2, p_bur_syslink_literal,
decode(mcacd.cost_element_id,3, p_wip_syslink_literal,
decode(mcacd.cost_element_id,4, p_wip_syslink_literal,
decode(mcacd.cost_element_id,5, p_bur_syslink_literal,
p_inv_syslink_literal)))) system_linkage,
'P' transaction_status_code ,
mcacd.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
cst_cost_elem_exp_types cceet,
cst_cg_item_costs_view ccicv
WHERE mcacd.transaction_id = p_transaction_id
AND mcacd.organization_id = p_organization_id
AND mcacd.inventory_item_id = p_inventory_item_id
AND mcacd.payback_variance_amount <> 0
AND mcacd.layer_id = decode(p_primary_cost_method,
1, -1, ccicv.layer_id) --PJMSTD
AND cceet.cost_element_id = mcacd.cost_element_id
AND ccicv.organization_id = p_organization_id
AND ccicv.inventory_item_id = p_inventory_item_id
AND ccicv.cost_group_id = decode(p_primary_cost_method,
1,1, p_cost_group_id) --PJMSTD
GROUP BY mcacd.transaction_id,
mcacd.cost_element_id,
cceet.expenditure_type_in,
cceet.expenditure_type_out;
SELECT decode(p_type_class,1,
p_cap_txn_source_literal,
p_inv_txn_source_literal) transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, l_organization_name,
29, l_organization_name, l_xfer_organization_name),
l_organization_name) organization_name,
p_transaction_date expenditure_item_date,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, l_project_number,
29, l_project_number, l_to_project_number),
l_project_number) project_number,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, l_task_number,
29, l_task_number, l_to_task_number),
l_task_number) task_number,
nvl(ppp.ppv_expenditure_type, pop.ppv_expenditure_type) expenditure_type,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, p_primary_quantity,
29, p_primary_quantity, (-1)*p_primary_quantity),
p_primary_quantity) quantity,
mmt.variance_amount raw_cost,
p_item_description expenditure_comment,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, to_char(p_transaction_id),
29, to_char(p_transaction_id),
21, to_char(p_transaction_id),
to_char(p_transfer_transaction_id)),
to_char(p_transaction_id)) orig_transaction_reference,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, mmt.variance_amount/p_primary_quantity,
29, mmt.variance_amount/p_primary_quantity,
(-1)*mmt.variance_amount/p_primary_quantity),
mmt.variance_amount/p_primary_quantity) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_proj_org_id org_id,
mmt.variance_amount burdened_cost,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, mmt.variance_amount/p_primary_quantity,
29, mmt.variance_amount/p_primary_quantity,
(-1)* mmt.variance_amount/p_primary_quantity),
mmt.variance_amount/p_primary_quantity) burdened_cost_rate,
p_inv_syslink_literal system_linkage,
'P' transaction_status_code ,
NULL cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_material_transactions mmt,
pjm_project_parameters ppp,
pjm_org_parameters pop
WHERE mmt.transaction_id = decode(ppv_txfr_flag,1, p_transfer_transaction_id,
p_transaction_id)
AND mmt.organization_id = decode(ppv_txfr_flag,1,p_transfer_organization_id,
p_organization_id)
AND NVL(mmt.variance_amount,0) <> 0
AND pop.organization_id = decode(sign(p_primary_quantity),-1,
decode( p_transaction_action_id,
1, p_organization_id,
29, p_organization_id,
p_transfer_organization_id),
p_organization_id)
AND ppp.organization_id (+) = pop.organization_id
AND ppp.project_id (+) = decode(sign(p_primary_quantity),-1,
decode( p_transaction_action_id,
1, p_project_id,
29, p_project_id, p_to_project_id),
p_project_id);
SELECT primary_cost_method, decode(pm_cost_collection_enabled, 1, 1, 0)
INTO l_primary_cost_method_snd, l_cost_collection_enabled_snd
FROM mtl_parameters
WHERE organization_id = p_transfer_organization_id ;
SELECT 'CC'|| substr( replace( lpad(
to_char(p_Group_Id,'9999999999999'),14,'0') ,' ','0'),-8)
INTO l_batch
FROM DUAL;
select org_information3
into l_operating_unit
from hr_organization_information
where organization_id = p_organization_id
and org_information_context ='Accounting Information';
SELECT max(pop.common_project_id)
INTO l_default_project
FROM pjm_org_parameters pop,
mtl_material_transactions mmt
WHERE pop.organization_id = mmt.organization_id
AND mmt.transaction_id = p_transaction_id;
SELECT hou.name
INTO l_organization_name
FROM hr_organization_units hou
WHERE hou.organization_id = p_exp_org_id;
SELECT haou.name
INTO l_recv_iss_organization_name
FROM hr_all_organization_units haou
WHERE haou.organization_id = p_organization_id;
SELECT hou.name
INTO l_xfer_organization_name
FROM hr_organization_units hou
WHERE hou.organization_id = p_transfer_organization_id;
SELECT currency_code
INTO l_xfer_currency_code
FROM cst_organization_definitions cod
WHERE cod.organization_id = p_transfer_organization_id;
SELECT ppa.org_id,
ppa.segment1 -- project number
INTO l_proj_org_id,
l_project_number
FROM pa_projects_all ppa
WHERE ppa.project_id = p_project_id;
SELECT segment1 -- project number
INTO l_project_number
FROM pa_projects_all
WHERE project_id = p_project_id; */
SELECT task_number
INTO l_task_number
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = p_task_id;
SELECT ppa.org_id,
ppa.segment1 -- project number
INTO l_to_proj_org_id,
l_to_project_number
FROM pa_projects_all ppa
WHERE ppa.project_id = p_to_project_id;
SELECT segment1 -- project number
INTO l_to_project_number
FROM pa_projects_all
WHERE project_id = p_to_project_id; */
SELECT task_number
INTO l_to_task_number
FROM pa_tasks
WHERE project_id = p_to_project_id
AND task_id = p_to_task_id;
SELECT ppa.org_id,
segment1
INTO l_source_proj_org_id,
l_source_project_number
FROM pa_projects_all ppa
WHERE ppa.project_id = p_source_project_id;
SELECT segment1 -- project number
INTO l_source_project_number
FROM pa_projects_all
WHERE project_id = p_source_project_id; */
SELECT task_number
INTO l_source_task_number
FROM pa_tasks
WHERE project_id = p_source_project_id
AND task_id = p_source_task_id;
SELECT nvl(MMT.fob_point, MSNV.fob_point) INTO l_fob_point
FROM mtl_shipping_network_view MSNV, mtl_material_transactions MMT
WHERE MSNV.from_organization_id = p_organization_id
AND MSNV.to_organization_id = p_transfer_organization_id
AND MMT.transaction_id = p_transaction_id;
SELECT nvl(MMT.fob_point, MSNV.fob_point) INTO l_fob_point
FROM mtl_shipping_network_view MSNV, mtl_material_transactions MMT
WHERE MSNV.from_organization_id = p_transfer_organization_id
AND MSNV.to_organization_id = p_organization_id
AND MMT.transaction_id = p_transaction_id;
SELECT schedule_close_date
INTO l_gl_date
FROM org_acct_periods oac
WHERE oac.organization_id = p_organization_id
AND oac.acct_period_id = p_acct_period_id;*/
SELECT NVL(MAX(macs.transaction_id),-99)
INTO l_earn_moh
FROM mtl_actual_cost_subelement macs
WHERE macs.transaction_id = p_transaction_id
AND macs.organization_id = p_organization_id
AND macs.actual_cost <> 0
AND macs.level_type = 1
AND macs.cost_element_id = 2;
anything to insert */
/* bug 4655264. Need to make sure that p_to_project_id is not nULL before transferring */
l_stmt_num := 106;
select NVL(MAX(macs.transaction_id),-99)
into l_earn_tomoh
from mtl_actual_cost_subelement macs
WHERE macs.transaction_id = p_transfer_transaction_id
AND macs.organization_id = p_transfer_organization_id
AND macs.actual_cost <> 0
AND macs.level_type = 1
AND macs.cost_element_id = 2;
SELECT transaction_type_id
INTO l_txn_type
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT
DECODE(sign(p_primary_quantity),1,
cceet.EXPENDITURE_TYPE_OUT,
cceet.EXPENDITURE_TYPE_IN)
INTO l_exp_type
FROM cst_cost_elem_exp_types cceet
WHERE cceet.cost_element_id = c_rec1.cost_element_id;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec1.transaction_source,
p_batch_name =>c_rec1.batch_name,
p_expenditure_ending_date =>
c_rec1.expenditure_ending_date,
p_employee_number =>c_rec1.employee_number,
p_organization_name =>
c_rec1.organization_name,
p_expenditure_item_date =>
c_rec1.expenditure_item_date,
p_project_number =>c_rec1.project_number,
p_task_number =>c_rec1.task_number,
p_expenditure_type =>l_exp_type,
p_pa_quantity =>c_rec1.quantity,
p_raw_cost =>c_rec1.raw_cost,
p_expenditure_comment =>
c_rec1.expenditure_comment,
p_orig_transaction_reference =>
c_rec1.orig_transaction_reference,
p_raw_cost_rate =>c_rec1.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec1.unmatched_negative_txn_flag,
p_gl_date =>c_rec1.gl_date,
p_org_id =>c_rec1.org_id,
p_burdened_cost =>c_rec1.burdened_cost,
p_burdened_cost_rate =>
c_rec1.burdened_cost_rate,
p_system_linkage =>c_rec1.system_linkage,
p_transaction_status_code =>
c_rec1.transaction_status_code,
p_denom_currency_code =>
c_rec1.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec1.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
RAISE NO_ROWS_TO_INSERT;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec2.transaction_source,
p_batch_name =>c_rec2.batch_name,
p_expenditure_ending_date =>
c_rec2.expenditure_ending_date,
p_employee_number =>c_rec2.employee_number,
p_organization_name =>
c_rec2.organization_name,
p_expenditure_item_date =>
c_rec2.expenditure_item_date,
p_project_number =>c_rec2.project_number,
p_task_number =>c_rec2.task_number,
p_expenditure_type =>c_rec2.expenditure_type,
p_pa_quantity =>c_rec2.quantity,
p_raw_cost =>c_rec2.raw_cost,
p_expenditure_comment =>
c_rec2.expenditure_comment,
p_orig_transaction_reference =>
c_rec2.orig_transaction_reference,
p_raw_cost_rate =>c_rec2.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec2.unmatched_negative_txn_flag,
p_gl_date =>c_rec2.gl_date,
p_org_id =>c_rec2.org_id,
p_burdened_cost =>c_rec2.burdened_cost,
p_burdened_cost_rate =>
c_rec2.burdened_cost_rate,
p_system_linkage =>c_rec2.system_linkage,
p_transaction_status_code =>
c_rec2.transaction_status_code,
p_denom_currency_code =>
c_rec2.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec2.cost_element_id,
p_resource_id =>c_rec2.resource_id,
p_source_flag =>-1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
RAISE NO_ROWS_TO_INSERT;
/* Insert Material Overhead absorption for the receiving side in a direct
interorg transaction */
If (l_earn_tomoh <> -99 AND p_transaction_id <> -99 ) then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Collecting the MOH absorption on the receiving side');
select organization_id into l_org_id
from mtl_material_transactions
where transaction_id = c_rec9.orig_transaction_reference;
select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled_flag,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = l_org_id ;
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_NO_ACCOUNTS';
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_ACCOUNTS';
SELECT pts1.transaction_source,
pts1.transaction_source,
pts2.transaction_source
INTO l_inv_txn_src_literal,
l_transaction_source,
l_cap_inv_txn_src_literal
FROM pa_transaction_sources pts1,
pa_transaction_sources pts2
WHERE pts1.transaction_source = 'Inventory'
AND pts2.transaction_source = 'Inventory Misc';
/* Now call insert routine to insert into interface */
pm_insert_pti_pvt
(p_transaction_source =>
l_transaction_source,
p_batch_name =>c_rec9.batch_name,
p_expenditure_ending_date =>
c_rec9.expenditure_ending_date,
p_employee_number =>c_rec9.employee_number,
p_organization_name =>
c_rec9.organization_name,
p_expenditure_item_date =>
c_rec9.expenditure_item_date,
p_project_number =>c_rec9.project_number,
p_task_number =>c_rec9.task_number,
p_expenditure_type =>c_rec9.expenditure_type,
p_pa_quantity =>c_rec9.quantity,
p_raw_cost =>c_rec9.raw_cost,
p_expenditure_comment =>
c_rec9.expenditure_comment,
p_orig_transaction_reference =>
c_rec9.orig_transaction_reference,
p_raw_cost_rate =>c_rec9.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec9.unmatched_negative_txn_flag,
p_gl_date =>c_rec9.gl_date,
p_org_id =>c_rec9.org_id,
p_burdened_cost =>c_rec9.burdened_cost,
p_burdened_cost_rate =>
c_rec9.burdened_cost_rate,
p_system_linkage =>c_rec9.system_linkage,
p_transaction_status_code =>
c_rec9.transaction_status_code,
p_denom_currency_code =>
c_rec9.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec9.cost_element_id,
p_resource_id =>c_rec9.resource_id,
p_source_flag =>1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
l_inv_txn_src_literal,
p_cap_txn_source_literal =>
l_cap_inv_txn_src_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
RAISE NO_ROWS_TO_INSERT;
SELECT
EXPENDITURE_TYPE
INTO
l_exp_type
FROM
MTL_MATERIAL_TRANSACTIONS
WHERE
transaction_id = p_transaction_id;
SELECT
decode(c_rec3.cost_element_id,1,
decode(p_transaction_source_type_id,1,
p_first_matl_se_exp_type,
decode(sign(p_primary_quantity),1,
cceet.EXPENDITURE_TYPE_IN,
cceet.EXPENDITURE_TYPE_OUT)),
decode(sign(p_primary_quantity),1,
cceet.EXPENDITURE_TYPE_IN,
cceet.EXPENDITURE_TYPE_OUT))
INTO l_exp_type
FROM cst_cost_elem_exp_types cceet
WHERE cceet.cost_element_id = c_rec3.cost_element_id;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec3.transaction_source,
p_batch_name =>c_rec3.batch_name,
p_expenditure_ending_date =>
c_rec3.expenditure_ending_date,
p_employee_number =>c_rec3.employee_number,
p_organization_name =>
NVL(l_recv_iss_organization_name,
c_rec3.organization_name),
p_expenditure_item_date =>
c_rec3.expenditure_item_date,
p_project_number =>c_rec3.project_number,
p_task_number =>c_rec3.task_number,
p_expenditure_type =>l_exp_type,
p_pa_quantity =>c_rec3.quantity,
p_raw_cost =>c_rec3.raw_cost,
p_expenditure_comment =>
c_rec3.expenditure_comment,
p_orig_transaction_reference =>
c_rec3.orig_transaction_reference,
p_raw_cost_rate =>c_rec3.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec3.unmatched_negative_txn_flag,
p_gl_date =>c_rec3.gl_date,
p_org_id =>c_rec3.org_id,
p_burdened_cost =>c_rec3.burdened_cost,
p_burdened_cost_rate =>
c_rec3.burdened_cost_rate,
p_system_linkage =>c_rec3.system_linkage,
p_transaction_status_code =>
c_rec3.transaction_status_code,
p_denom_currency_code =>
c_rec3.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec3.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>-1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
/* bug 3978501 commenting out the excpetion raise as there maybe PPV to insert */
/* RAISE NO_ROWS_TO_INSERT; */
SELECT EXPENDITURE_TYPE
INTO c_rec4.EXPENDITURE_TYPE
FROM MTL_MATERIAL_TRANSACTIONS
WHERE transaction_id = p_transaction_id;
select organization_id into l_org_id
from mtl_material_transactions
where transaction_id = c_rec4.orig_transaction_reference;
select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled_flag,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = l_org_id ;
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_NO_ACCOUNTS';
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_ACCOUNTS';
SELECT pts1.transaction_source,
pts1.transaction_source,
pts2.transaction_source
INTO l_inv_txn_src_literal,
l_transaction_source,
l_cap_inv_txn_src_literal
FROM pa_transaction_sources pts1,
pa_transaction_sources pts2
WHERE pts1.transaction_source = 'Inventory'
AND pts2.transaction_source = 'Inventory Misc';
pm_insert_pti_pvt
(p_transaction_source =>
l_transaction_source,
p_batch_name =>c_rec4.batch_name,
p_expenditure_ending_date =>
c_rec4.expenditure_ending_date,
p_employee_number =>c_rec4.employee_number,
p_organization_name =>
c_rec4.organization_name,
p_expenditure_item_date =>
c_rec4.expenditure_item_date,
p_project_number =>c_rec4.project_number,
p_task_number =>c_rec4.task_number,
p_expenditure_type =>c_rec4.expenditure_type,
p_pa_quantity =>c_rec4.quantity,
p_raw_cost =>c_rec4.raw_cost,
p_expenditure_comment =>
c_rec4.expenditure_comment,
p_orig_transaction_reference =>
c_rec4.orig_transaction_reference,
p_raw_cost_rate =>c_rec4.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec4.unmatched_negative_txn_flag,
p_gl_date =>c_rec4.gl_date,
p_org_id =>c_rec4.org_id,
p_burdened_cost =>c_rec4.burdened_cost,
p_burdened_cost_rate =>
c_rec4.burdened_cost_rate,
p_system_linkage =>c_rec4.system_linkage,
p_transaction_status_code =>
c_rec4.transaction_status_code,
p_denom_currency_code =>
c_rec4.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec4.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
l_inv_txn_src_literal,
p_cap_txn_source_literal =>
l_cap_inv_txn_src_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
If the receiving txn has not been costed yet, update MMT with a
warning but do not reset the pm_cost_collected flag to error.
*/
SELECT mmt.costed_flag
INTO l_costed_flag
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = decode(p_transaction_action_id,
3, p_transfer_transaction_id ,
p_transaction_id)
AND mmt.organization_id = decode(p_transaction_action_id,
3, p_transfer_organization_id,
p_organization_id);
/* bug 3978501 commenting out the excpetion part as there may be PPV to insert */
/* RAISE NO_ROWS_TO_INSERT; */
SELECT
decode(sign(p_primary_quantity),1,
cceet.EXPENDITURE_TYPE_OUT,
cceet.EXPENDITURE_TYPE_IN)
INTO l_exp_type
FROM cst_cost_elem_exp_types cceet
WHERE cceet.cost_element_id = c_rec5.cost_element_id;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec5.transaction_source,
p_batch_name =>c_rec5.batch_name,
p_expenditure_ending_date =>
c_rec5.expenditure_ending_date,
p_employee_number =>c_rec5.employee_number,
p_organization_name =>
c_rec5.organization_name,
p_expenditure_item_date =>
c_rec5.expenditure_item_date,
p_project_number =>c_rec5.project_number,
p_task_number =>c_rec5.task_number,
p_expenditure_type =>l_exp_type,
p_pa_quantity =>c_rec5.quantity,
p_raw_cost =>c_rec5.raw_cost,
p_expenditure_comment =>
c_rec5.expenditure_comment,
p_orig_transaction_reference =>
c_rec5.orig_transaction_reference,
p_raw_cost_rate =>c_rec5.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec5.unmatched_negative_txn_flag,
p_gl_date =>c_rec5.gl_date,
p_org_id =>c_rec5.org_id,
p_burdened_cost =>c_rec5.burdened_cost,
p_burdened_cost_rate =>
c_rec5.burdened_cost_rate,
p_system_linkage =>c_rec5.system_linkage,
p_transaction_status_code =>
c_rec5.transaction_status_code,
p_denom_currency_code =>
c_rec5.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec5.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
RAISE NO_ROWS_TO_INSERT;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec6.transaction_source,
p_batch_name =>c_rec6.batch_name,
p_expenditure_ending_date =>
c_rec6.expenditure_ending_date,
p_employee_number =>c_rec6.employee_number,
p_organization_name =>
c_rec6.organization_name,
p_expenditure_item_date =>
c_rec6.expenditure_item_date,
p_project_number =>c_rec6.project_number,
p_task_number =>c_rec6.task_number,
p_expenditure_type =>c_rec6.expenditure_type,
p_pa_quantity =>c_rec6.quantity,
p_raw_cost =>c_rec6.raw_cost,
p_expenditure_comment =>
c_rec6.expenditure_comment,
p_orig_transaction_reference =>
c_rec6.orig_transaction_reference,
p_raw_cost_rate =>c_rec6.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec6.unmatched_negative_txn_flag,
p_gl_date =>c_rec6.gl_date,
p_org_id =>c_rec6.org_id,
p_burdened_cost =>c_rec6.burdened_cost,
p_burdened_cost_rate =>
c_rec6.burdened_cost_rate,
p_system_linkage =>c_rec6.system_linkage,
p_transaction_status_code =>
c_rec6.transaction_status_code,
p_denom_currency_code =>
c_rec6.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec6.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>-1,
p_variance_flag =>1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
SELECT
decode(sign(-1 * p_primary_quantity),1,
cceet.EXPENDITURE_TYPE_OUT,
cceet.EXPENDITURE_TYPE_IN)
INTO l_exp_type
FROM cst_cost_elem_exp_types cceet
WHERE cceet.cost_element_id = c_rec6.cost_element_id;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec6.transaction_source,
p_batch_name =>c_rec6.batch_name,
p_expenditure_ending_date =>
c_rec6.expenditure_ending_date,
p_employee_number =>c_rec6.employee_number,
p_organization_name =>
c_rec6.organization_name,
p_expenditure_item_date =>
c_rec6.expenditure_item_date,
p_project_number =>l_to_project_number,
p_task_number =>l_to_task_number,
p_expenditure_type =>l_exp_type,
p_pa_quantity =>-1 * c_rec6.quantity,
p_raw_cost =>-1 * c_rec6.raw_cost,
p_expenditure_comment =>
c_rec6.expenditure_comment,
p_orig_transaction_reference =>to_char(p_transfer_transaction_id),
p_raw_cost_rate =>-1 * c_rec6.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec6.unmatched_negative_txn_flag,
p_gl_date =>c_rec6.gl_date,
p_org_id =>l_to_proj_org_id,
p_burdened_cost =>-1 * c_rec6.burdened_cost,
p_burdened_cost_rate =>
-1 * c_rec6.burdened_cost_rate,
p_system_linkage =>c_rec6.system_linkage,
p_transaction_status_code =>
c_rec6.transaction_status_code,
p_denom_currency_code =>
c_rec6.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec6.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>-1,
p_variance_flag =>1,
p_primary_quantity =>-1 * p_primary_quantity ,
p_transfer_organization_id =>
p_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_to_project_id,
p_task_id =>p_to_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_transfer_cost_group_id,
p_transfer_cost_group_id =>
p_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_project_id,
p_to_task_id =>p_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
RAISE NO_ROWS_TO_INSERT;
select organization_id into l_org_id
from mtl_material_transactions
where transaction_id = c_rec7.orig_transaction_reference;
select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled_flag,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = l_org_id ;
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_NO_ACCOUNTS';
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_ACCOUNTS';
SELECT pts1.transaction_source,
pts2.transaction_source
INTO l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
FROM pa_transaction_sources pts1,
pa_transaction_sources pts2
WHERE pts1.transaction_source = 'Inventory'
AND pts2.transaction_source = 'Inventory Misc';
SELECT decode(p_type_class,1,
l_cap_inv_txn_src_literal,
l_inv_txn_src_literal)
INTO l_transaction_source
from dual;
pm_insert_pti_pvt
(p_transaction_source =>
l_transaction_source,
p_batch_name =>c_rec7.batch_name,
p_expenditure_ending_date =>
c_rec7.expenditure_ending_date,
p_employee_number =>c_rec7.employee_number,
p_organization_name =>
c_rec7.organization_name,
p_expenditure_item_date =>
c_rec7.expenditure_item_date,
p_project_number =>c_rec7.project_number,
p_task_number =>c_rec7.task_number,
p_expenditure_type =>c_rec7.expenditure_type,
p_pa_quantity =>c_rec7.quantity,
p_raw_cost =>c_rec7.raw_cost,
p_expenditure_comment =>
c_rec7.expenditure_comment,
p_orig_transaction_reference =>
c_rec7.orig_transaction_reference,
p_raw_cost_rate =>c_rec7.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec7.unmatched_negative_txn_flag,
p_gl_date =>c_rec7.gl_date,
p_org_id =>c_rec7.org_id,
p_burdened_cost =>c_rec7.burdened_cost,
p_burdened_cost_rate =>
c_rec7.burdened_cost_rate,
p_system_linkage =>c_rec7.system_linkage,
p_transaction_status_code =>
c_rec7.transaction_status_code,
p_denom_currency_code =>
c_rec7.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec7.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>-1,
p_variance_flag =>1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
l_inv_txn_src_literal,
p_cap_txn_source_literal =>
l_cap_inv_txn_src_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
/* bug 3978501. check to see if r have been inserted and update l_no_ppv */
if l_rownum <> 0 then
l_no_ppv := 0;
RAISE NO_ROWS_TO_INSERT;
WHEN NO_ROWS_TO_INSERT THEN
O_err_num := 20002;
WHEN CST_FAILED_INSERT_PTI THEN
O_err_num := 20005;
SELECT decode(br.expenditure_type,NULL,
decode(br1.expenditure_type,NULL,'NO VALUE',
br1.expenditure_type),
br.expenditure_type)
INTO l_exp_type
FROM mtl_parameters mp,
cst_item_cost_details cicd,
bom_resources br,
bom_resources br1
WHERE mp.organization_id = p_organization_id
AND mp.cost_organization_id = cicd.organization_id (+)
AND cicd.inventory_item_id (+) = p_inventory_item_id
AND cicd.cost_type_id (+) = decode(p_cost_method, 1, 1,
p_avg_rates_cost_type_id)
AND cicd.cost_element_id (+) = 1
AND cicd.organization_id = br.organization_id (+)
AND cicd.resource_id = br.resource_id (+)
AND mp.cost_organization_id = br1.organization_id (+)
AND mp.default_material_cost_id = br1.resource_id (+)
AND rownum=1;
SELECT 'NO VALUE'
INTO l_exp_type
FROM mtl_actual_cost_subelement macs,
bom_resources br
WHERE macs.transaction_id = p_transaction_id
AND macs.organization_id = p_organization_id
AND macs.cost_element_id = 2
AND macs.level_type = 1
AND br.RESOURCE_ID = macs.RESOURCE_ID
AND br.ORGANIZATION_ID = macs.ORGANIZATION_ID
AND br.expenditure_type IS NULL;
/* update the errorcode and error_explanation fields inspite of the
maintenance of the log file */
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = 'E',
mmt.error_code = p_error_code,
mmt.error_explanation = p_error_explanation,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.transaction_id = p_transaction_id ;
SELECT p_wip_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
p_employee_number employee_number,
p_exp_org_name organization_name,
p_transaction_date expenditure_item_date,
l_project_number project_number,
l_task_number task_number,
p_expenditure_type expenditure_type,
p_denom_currency_code denom_currency_code,
decode(p_cost_element_id,
5,0,
decode(p_primary_quantity,
NULL,p_base_transaction_value,
p_primary_quantity)) quantity,
decode(p_cost_element_id,5,0, p_base_transaction_value)
raw_cost,
p_resource_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(p_cost_element_id,
5,0,
decode(p_primary_quantity,
NULL, 1,
0, p_base_transaction_value,
p_base_transaction_value / p_primary_quantity))
raw_cost_rate,
'Y' unmatched_negative_txn_flag,
p_reference_account dr_code_combination_id,
p_cr_account cr_code_combination_id,
p_wip_dr_sub_ledger_id wip_dr_sub_ledger_id,
p_wip_cr_sub_ledger_id wip_cr_sub_ledger_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_multi_org_id org_id,
p_base_transaction_value burdened_cost,
decode(p_base_transaction_value,0,0,
decode(p_primary_quantity,
NULL,1,
0,p_base_transaction_value,
p_base_transaction_value/p_primary_quantity))
burdened_cost_rate,
decode(p_cost_element_id,5,
p_bur_syslink_literal, p_wip_syslink_literal)
system_linkage,
'P' transaction_status_code
FROM dual
WHERE p_base_transaction_value <> 0;
SELECT 'CC'|| substr( replace( lpad(
to_char(p_Group_Id,'9999999999999'),14,'0') ,' ','0'),-8)
INTO l_batch
FROM DUAL;
select org_information3
into l_operating_unit
from hr_organization_information
where organization_id = p_organization_id
and org_information_context ='Accounting Information';
SELECT schedule_close_date
INTO l_gl_date
FROM org_acct_periods oap
WHERE oap.organization_id = p_organization_id
AND oap.acct_period_id = p_acct_period_id;*/
SELECT segment1 -- project number
INTO l_project_number
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT task_number
INTO l_task_number
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = p_task_id;
select to_number(org_information3)
into l_multi_org_id
from hr_organization_information
where organization_id = p_organization_id
and org_information_context ='Accounting Information';
Select NVL(system_linkage_function,c_rec.system_linkage) into l_syslinkage
from pa_transaction_sources
where transaction_source = l_transaction_source;
/* This following insert statement into pa_transaction_interface will be changes to insert into pa_transaction_interface_all */
/* modify the insert statement to insert the wip_resource_id and primary UOM code bug 3298023 */
/* get the primary UOM code directly from the transaction */
select primary_uom into l_uom_code
from wip_transactions
where transaction_id = p_transaction_id ;
/* This following insert statement into pa_transaction_interface will be changes to insert into pa_transaction_interface_all */
INSERT INTO pa_transaction_interface_all
( transaction_source,
batch_name,
expenditure_ending_date,
employee_number,
organization_name,
expenditure_item_date,
project_number,
task_number,
expenditure_type,
quantity,
denom_raw_cost,
acct_raw_cost,
expenditure_comment,
orig_transaction_reference,
raw_cost_rate,
unmatched_negative_txn_flag,
dr_code_combination_id,
cr_code_combination_id,
cdl_system_reference1,
cdl_system_reference2,
cdl_system_reference3,
gl_date,
org_id,
denom_burdened_cost,
acct_burdened_cost,
burdened_cost_rate,
system_linkage,
transaction_status_code,
denom_currency_code,
person_business_group_name,
wip_resource_id,
unit_of_measure,
cdl_system_reference4, --WIP cr. sub Ledger ID
cdl_system_reference5 --WIP Dr. sub LEDGER ID
)
VALUES
( l_transaction_source,
c_rec.batch_name,
c_rec.expenditure_ending_date,
c_rec.employee_number,
c_rec.organization_name,
c_rec.expenditure_item_date,
c_rec.project_number,
c_rec.task_number,
c_rec.expenditure_type,
c_rec.quantity,
l_raw_cost,
l_raw_cost,
c_rec.expenditure_comment,
c_rec.orig_transaction_reference,
l_raw_cost_rate,
c_rec.unmatched_negative_txn_flag,
l_dr_code_combination_id,
l_cr_code_combination_id,
c_rec.cdl_system_reference1,
c_rec.cdl_system_reference2,
c_rec.cdl_system_reference3,
c_rec.gl_date,
c_rec.org_id,
l_burdened_cost,
l_burdened_cost,
l_burdened_cost_rate,
l_syslinkage,
c_rec.transaction_status_code,
c_rec.denom_currency_code,
p_business_group_name,
p_resource_id,
l_uom_code,
l_wip_cr_sub_ledger_id,
l_wip_dr_sub_ledger_id
);
SELECT hou.name
INTO l_organization_name
FROM bom_departments bd,
hr_organization_units hou
WHERE hou.organization_id =
bd.pa_expenditure_org_id
AND bd.organization_id = p_organization_id
AND bd.department_id = p_department_id;
UPDATE wip_transactions wt
SET wt.pm_cost_collected = 'E',
wt.last_update_date = sysdate,
wt.last_updated_by = p_user_id,
wt.last_update_login = p_login_id,
wt.request_id = p_req_id,
wt.program_application_id = p_prg_appl_id,
wt.program_id = p_prg_id,
wt.program_update_date = sysdate
WHERE wt.transaction_id = p_transaction_id ;
UPDATE wip_txn_interface_errors wtie
SET wtie.error_message = p_error_explanation,
wtie.last_update_date = sysdate,
wtie.last_updated_by = p_user_id,
wtie.last_update_login = p_login_id,
wtie.request_id = p_req_id,
wtie.program_application_id = p_prg_appl_id,
wtie.program_id = p_prg_id,
wtie.program_update_date = sysdate
WHERE wtie.error_column='PM_COST_COLLECTED'
AND wtie.transaction_id = p_transaction_id;
INSERT INTO wip_txn_interface_errors
( transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES ( p_transaction_id,
p_error_explanation,
'PM_COST_COLLECTED',
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_req_id,
p_prg_appl_id,
p_prg_id,
sysdate
);
SELECT
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.organization_id,
mmt.transfer_organization_id,
mmt.transfer_transaction_id,
mmt.cost_group_id,
mmt.transfer_cost_group_id,
mmt.transfer_subinventory,
mmt.inventory_item_id,
mmt.primary_quantity,
mmt.transaction_type_id,
mtt.type_class
INTO l_transaction_action_id,
l_transaction_source_type_id,
l_organization_id,
l_xfer_organization_id,
l_xfer_transaction_id,
l_cg_id,
l_xfer_cg_id,
l_transfer_subinventory,
l_inventory_item_id,
l_mmt_primary_quantity,
l_txn_type_id,
l_type_class
FROM mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE mmt.transaction_id = p_transaction_id
AND mtt.transaction_type_id = mmt.transaction_type_id;
SELECT mmt.subinventory_code,
msi.asset_inventory
INTO
l_subinventory_code,
l_si_asset_yes_no
FROM MTL_MATERIAL_TRANSACTIONS mmt,
MTL_SECONDARY_INVENTORIES msi
WHERE
mmt.transaction_id = p_transaction_id
AND msi.secondary_inventory_name = mmt.subinventory_code
AND msi.organization_id = mmt.organization_id;
SELECT decode(inventory_asset_flag, 'Y',0,1)
INTO l_exp_item
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_inventory_item_id
AND msi.organization_id = l_organization_id;
SELECT decode(l_exp_item,1,1,decode(l_si_asset_yes_no,1,0,1))
INTO l_exp_flag
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = l_subinventory_code
AND msi.organization_id = l_organization_id;
SELECT msub.asset_inventory,
decode(mitems.inventory_asset_flag,
'Y', decode(msub.asset_inventory,1,0,1), 1)
INTO l_transfer_si_asset_yes_no,
l_xfer_exp_flag
FROM mtl_secondary_inventories msub,
mtl_system_items mitems
WHERE msub.secondary_inventory_name = l_transfer_subinventory
AND msub.organization_id = l_xfer_organization_id
AND mitems.inventory_item_id = l_inventory_item_id
AND mitems.organization_id = l_xfer_organization_id;
SELECT nvl(MMT.fob_point, MSNV.fob_point) INTO l_fob_point
FROM mtl_shipping_network_view MSNV,
mtl_material_transactions MMT
WHERE MSNV.from_organization_id = l_organization_id
AND MSNV.to_organization_id = l_xfer_organization_id
AND MMT.transaction_id = p_transaction_id;
SELECT nvl(MMT.fob_point, MSNV.fob_point)
INTO l_fob_point
FROM mtl_shipping_network_view MSNV,
mtl_material_transactions MMT
WHERE MSNV.from_organization_id = l_xfer_organization_id
AND MSNV.to_organization_id = l_organization_id
AND MMT.transaction_id = p_transaction_id;
SELECT primary_cost_method
INTO l_cost_method
FROM mtl_parameters
WHERE organization_id = l_organization_id;
SELECT primary_cost_method
INTO l_xfer_cost_method
FROM mtl_parameters
WHERE organization_id = l_xfer_organization_id;
SELECT NVL(MAX(mta.reference_account), -999999)
INTO O_dr_code_combination_id
FROM mtl_transaction_accounts mta
WHERE
mta.transaction_id = l_mta_transaction_id AND
mta.organization_id = l_mta_organization_id AND
mta.inventory_item_id = l_inventory_item_id AND
nvl(mta.cost_element_id, -99) = l_cost_element_id AND
mta.accounting_line_type = l_accounting_line_type AND
mta.primary_quantity = decode(p_variance_flag, 1, mta.primary_quantity,
decode(l_source_flag, 1,
decode(l_transaction_action_id, 3, mta.primary_quantity,
l_mta_primary_quantity), l_mta_primary_quantity)) AND
(((l_citw_flag = 1) AND
(mta.transaction_source_type_id = l_wip_txn_source_type)) OR(l_citw_flag <> 1));
SELECT MAX(INV_SUB_LEDGER_ID)
INTO O_inv_dr_sub_ledger_id
FROM mtl_transaction_accounts mta
WHERE
mta.reference_account = O_dr_code_combination_id AND
mta.transaction_id = l_mta_transaction_id AND
mta.organization_id = l_mta_organization_id AND
mta.inventory_item_id = l_inventory_item_id AND
nvl(mta.cost_element_id, -99) = l_cost_element_id AND
mta.accounting_line_type = l_accounting_line_type AND
mta.primary_quantity = decode(p_variance_flag, 1, mta.primary_quantity,
decode(l_source_flag, 1,
decode(l_transaction_action_id, 3, mta.primary_quantity,
l_mta_primary_quantity), l_mta_primary_quantity)) AND
(((l_citw_flag = 1) AND
(mta.transaction_source_type_id = l_wip_txn_source_type)) OR(l_citw_flag <> 1));
SELECT
NVL((MAX(mta.reference_account)),-999999)
INTO O_cr_code_combination_id
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = p_transaction_id
AND mta.organization_id = l_organization_id
AND mta.inventory_item_id = l_inventory_item_id
AND NVL(mta.cost_element_id,-99)= NVL(p_cost_element_id,-99)
AND mta.accounting_line_type = 1
AND mta.primary_quantity = decode(l_source_flag,1,l_mmt_primary_quantity,((-1)*l_mmt_primary_quantity))
AND mta.transaction_source_type_id = 13; --ALWAYS INV
Select MAX(INV_SUB_LEDGER_ID)
into O_inv_cr_sub_ledger_id
from mtl_transaction_accounts mta
where mta.reference_account = O_cr_code_combination_id
AND mta.transaction_id = p_transaction_id
AND mta.organization_id = l_organization_id
AND mta.inventory_item_id = l_inventory_item_id
AND NVL(mta.cost_element_id,-99)= NVL(p_cost_element_id,-99)
AND mta.accounting_line_type = 1
AND mta.primary_quantity = decode(l_source_flag,1,l_mmt_primary_quantity,((-1)*l_mmt_primary_quantity))
AND mta.transaction_source_type_id = 13; --ALWAYS INV
Select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = l_mta_organization_id;
SELECT NVL(MAX(pjm_clearing_account),-999999)
INTO O_cr_code_combination_id
FROM pjm_org_parameters
where organization_id = l_mta_organization_id;
select (decode(sign(l_mmt_primary_quantity),1,10,9))
into l_accounting_line_type
from dual;
SELECT
decode(l_transaction_action_id, 3, --Direct IO Xfer
decode(sign(l_mmt_primary_quantity), 1,9, --IO Pyble
10), --IO Rcvble
32, 7, --Assy Return
31, 7, --Assy Complete
33, 7, --Neg WIP Issue
34, 7, --Neg WIP Return
2, decode(l_xfer_exp_flag, 0,1, 2), --Sub Xfer
4, 2, --Cycle Count
8, 2, --Phy Inv
12, --Intransit Receipt
decode(nvl(l_fob_point,-99), 1,14, 2,9), 21, --Intransit Shipment
decode(nvl(l_fob_point,-99), 1,10, 2,14), 29, decode(l_transaction_source_type_id, 1,5), --PO Adj
1, decode(l_transaction_source_type_id, 1, 5, --RTV
3, 2, --A/C Issue
5, 7, --WIP/CFM
6, 2, --Ali Issue
13,2), --Misc Issue
27, decode(l_transaction_source_type_id, 1, 5, --PO Receipt
3, 2, --A/C Receipt
5, 7, --WIP/CFM
6, 2, --Ali Receipt
13,2), --Misc Rcpt
6,16, /*consigned ownership transfer */
26,31, /* Logical PO receipt for BP org */
28,1, /* staging Txfrs */
17, 9, /* Logical Expense Requition Receipt */ -99)
INTO l_accounting_line_type
FROM DUAL;
Select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = l_mta_organization_id;
SELECT NVL(MAX(pjm_clearing_account),-999999)
INTO O_cr_code_combination_id
FROM pjm_org_parameters
where organization_id = l_mta_organization_id;
SELECT NVL(MAX(mta.reference_account), -999999)
INTO O_cr_code_combination_id
FROM mtl_transaction_accounts mta
WHERE
mta.transaction_id =l_mta_transaction_id AND
mta.organization_id =l_mta_organization_id AND
mta.inventory_item_id =l_inventory_item_id AND
nvl(mta.cost_element_id, -99) =decode(l_cost_element_id,-99,nvl(mta.cost_element_id,-99),l_cost_element_id) AND
nvl(mta.resource_id, -99) = nvl(p_resource_id,-99) AND
mta.accounting_line_type = l_accounting_line_type AND
mta.primary_quantity = l_mta_primary_quantity;
SELECT MAX(INV_SUB_LEDGER_ID)
INTO O_inv_cr_sub_ledger_id
FROM mtl_transaction_accounts mta
WHERE
mta.reference_account = O_cr_code_combination_id AND
mta.transaction_id =l_mta_transaction_id AND
mta.organization_id =l_mta_organization_id AND
mta.inventory_item_id =l_inventory_item_id AND
nvl(mta.cost_element_id, -99) =decode(l_cost_element_id,-99,nvl(mta.cost_element_id,-99),l_cost_element_id) AND
nvl(mta.resource_id, -99) = nvl(p_resource_id,-99) AND
mta.accounting_line_type = l_accounting_line_type AND
mta.primary_quantity = l_mta_primary_quantity;
| it will manage the insertion of records in PA_TRANSACTIONS_INTERFACE |
| |
| PARAMETERS |
| |
| |
| CALLED FROM |
| pm_process_txn_mmt |
| |
| HISTORY |
| 23-APR-01 Hemant Gosain Created. |
*----------------------------------------------------------------------------*/
PROCEDURE pm_insert_pti_pvt
(p_transaction_source VARCHAR2,
p_batch_name VARCHAR2,
p_expenditure_ending_date DATE,
p_employee_number VARCHAR2,
p_organization_name VARCHAR2,
p_expenditure_item_date DATE,
p_project_number VARCHAR2,
p_task_number VARCHAR2,
p_expenditure_type VARCHAR2,
p_pa_quantity NUMBER,
p_raw_cost NUMBER,
p_expenditure_comment VARCHAR2,
p_orig_transaction_reference VARCHAR2,
p_raw_cost_rate NUMBER,
p_unmatched_negative_txn_flag VARCHAR2,
p_gl_date DATE,
p_org_id NUMBER,
p_burdened_cost NUMBER,
p_burdened_cost_rate NUMBER,
p_system_linkage VARCHAR2,
p_transaction_status_code VARCHAR2,
p_denom_currency_code VARCHAR2,
p_transaction_id NUMBER,
p_transaction_action_id NUMBER,
p_transaction_source_type_id NUMBER,
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_cost_element_id NUMBER,
p_resource_id NUMBER,
p_source_flag NUMBER,
p_variance_flag NUMBER,
p_primary_quantity NUMBER,
p_transfer_organization_id NUMBER,
p_fob_point NUMBER,
p_wip_entity_id NUMBER,
p_basis_resource NUMBER,
p_type_class NUMBER,
p_project_id NUMBER,
p_task_id NUMBER,
p_transaction_date DATE,
p_cost_group_id NUMBER,
p_transfer_cost_group_id NUMBER,
p_transaction_source_id NUMBER,
p_to_project_id NUMBER,
p_to_task_id NUMBER,
p_source_project_id NUMBER,
p_source_task_id NUMBER,
p_transfer_transaction_id NUMBER,
p_primary_cost_method NUMBER,
p_acct_period_id NUMBER,
p_exp_org_id NUMBER,
p_distribution_account_id NUMBER,
p_proj_job_ind NUMBER,
p_first_matl_se_exp_type VARCHAR2,
p_inv_txn_source_literal VARCHAR2,
p_cap_txn_source_literal VARCHAR2,
p_inv_syslink_literal VARCHAR2,
p_bur_syslink_literal VARCHAR2,
p_wip_syslink_literal VARCHAR2,
p_user_def_exp_type VARCHAR2,
p_flow_schedule VARCHAR2,
p_si_asset_yes_no NUMBER,
p_transfer_si_asset_yes_no NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2
)
IS
l_err_num NUMBER := 0;
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEBUG: Reached#:pm_insert_pti_pvt');
select to_number(org_information3)
into l_op_unit
from hr_organization_information hoi,
mtl_material_transactions mmt
where hoi.organization_id = mmt.organization_id
and mmt.transaction_id = to_number(p_orig_transaction_reference)
and org_information_context ='Accounting Information';
SELECT nvl(comms_nl_trackable_flag, 'N'), asset_creation_code
INTO l_nl_trackable, l_asset_creation_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEBUG: Inserting transaction into PTI ');
/* The following insert statement into pa_transaction_interface will be changed to insert into pa_transaction_interface_all */
/* The following insert statement into pa_transaction_interface will be changed to insert into pa_transaction_interface_all */
/* Modified the insert to insert the Inventory_item_id and basic UOM code bug 3298023 */
/*Get the primary UOM code of the item from mtl_system_items */
select primary_uom_code into l_uom_code
from mtl_system_items msi
where msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id;
INSERT INTO pa_transaction_interface_all
( transaction_source,
batch_name,
expenditure_ending_date,
employee_number,
organization_name,
expenditure_item_date,
project_number,
task_number,
expenditure_type,
quantity,
denom_raw_cost,
acct_raw_cost,
expenditure_comment,
orig_transaction_reference,
raw_cost_rate,
unmatched_negative_txn_flag,
dr_code_combination_id,
cr_code_combination_id,
cdl_system_reference1,
cdl_system_reference2,
cdl_system_reference3,
gl_date,
org_id,
denom_burdened_cost,
acct_burdened_cost,
burdened_cost_rate,
system_linkage,
transaction_status_code,
denom_currency_code,
Inventory_item_id,
unit_of_measure,
cdl_system_reference4, --Credit INV Sub ledger ID
cdl_system_reference5 --Debit INV Subledger ID
)
VALUES
(
p_transaction_source,
p_batch_name,
p_expenditure_ending_date,
p_employee_number,
p_organization_name,
p_expenditure_item_date,
p_project_number,
p_task_number,
p_expenditure_type,
p_pa_quantity,
l_raw_cost,
l_raw_cost,
p_expenditure_comment,
p_orig_transaction_reference,
l_raw_cost_rate,
p_unmatched_negative_txn_flag,
l_dr_code_combination_id,
l_cr_code_combination_id,
NULL,
NULL,
NULL,
p_gl_date,
l_op_unit,
l_burdened_cost,
l_burdened_cost,
l_burdened_cost_rate,
l_systemlinkage,
p_transaction_status_code,
p_denom_currency_code,
p_inventory_item_id,
l_uom_code,
l_inv_cr_sub_ledger_id,
l_inv_dr_sub_ledger_id
);
O_err_code := SUBSTR('CSTPPCCB.pm_insert_pti_pvt('
|| to_char(l_stmt_num)
|| '): '
|| 'FAILED CSE Package Call. '
|| l_cse_err_code,1,240);
O_err_code := SUBSTR('CSTPPCCB.pm_insert_pti_pvt('
|| to_char(l_stmt_num)
|| '): '
|| 'FAILED TO GET ACCT. '
|| l_err_msg,1,240);
O_err_code := 'CSTPPCCB.pm_insert_pti_pvt('
|| to_char(l_stmt_num)
|| '): ';
O_err_msg := SUBSTR('CSTPPCCB.pm_insert_pti_pvt('
||to_char(l_stmt_num)
||'), '
||'CSTCCHKB.pm_invtxn_hook: '
||'error at CSTCCHKB, line: '
||to_char(l_err_num)
||' CSTCCHKB.err_msg:'
||l_err_msg,1,240);
O_err_msg := SUBSTR('CSTPPCCB.pm_insert_pti_pvt('
|| to_char(l_stmt_num)
|| '): '
||SQLERRM,1,240);
END pm_insert_pti_pvt;