APPS.CSTPMECS dependencies on CST_OPEN_POS_TEMP

Line 486: /* Populate the global temporary table cst_open_pos_temp with all of the items */

482:
483: CSTPUTIL.CSTPUGCI(I_ORG_ID, ROUND_UNIT, PRECISION, EXT_PREC);
484:
485: /*=============================================================================*/
486: /* Populate the global temporary table cst_open_pos_temp with all of the items */
487: /* in cst_lists that have open POs associated with them. This data will be */
488: /* used below to update the item cost and usage rate in CICD. */
489: /*=============================================================================*/
490:

Line 497: INSERT INTO cst_open_pos_temp (

493: * the explain plan for the single insert has a cost in the 10,000 order of
494: * magnitude, vs. a 10 order of magnitude cost for the update statement.
495: */
496:
497: INSERT INTO cst_open_pos_temp (
498: usage_rate_or_amount,
499: inventory_item_id)
500: SELECT
501: NULL

Line 507: UPDATE cst_open_pos_temp COPT

503: FROM cst_lists
504: WHERE list_id = I_LIST_ID;
505:
506:
507: UPDATE cst_open_pos_temp COPT
508: set usage_rate_or_amount =
509: (SELECT (SUM((POD.quantity_ordered - NVL(POD.quantity_delivered,0)) *
510: (PLL.price_override + nvl(po_tax_sv.get_tax('PO',pod.po_distribution_id),0)/pod.quantity_ordered) *
511: NVL(POD.rate,1)) /

Line 548: DELETE FROM cst_open_pos_temp

544:
545: fnd_file.put_line(fnd_file.log,'Inserted '||to_char(SQL%ROWCOUNT)||' rows into temp table.');
546:
547: /* Now delete all items from the temp tables where there were no OPEN POs found */
548: DELETE FROM cst_open_pos_temp
549: WHERE usage_rate_or_amount IS NULL;
550:
551: DELETE FROM cst_lists
552: WHERE list_id = I_LIST_ID

Line 553: AND entity_id NOT IN (SELECT inventory_item_id FROM cst_open_pos_temp);

549: WHERE usage_rate_or_amount IS NULL;
550:
551: DELETE FROM cst_lists
552: WHERE list_id = I_LIST_ID
553: AND entity_id NOT IN (SELECT inventory_item_id FROM cst_open_pos_temp);
554:
555: /*===============================================================*/
556: /* insert item cost where there is open PO but no pending cost */
557: /* information */

Line 574: /* Update CICD using the data in the global temporary table cst_open_pos_temp. */

570: raise_application_error(-20001, 'CSTPOPOA: '||SQLERRM);
571: END IF;
572:
573: /*=============================================================================*/
574: /* Update CICD using the data in the global temporary table cst_open_pos_temp. */
575: /*=============================================================================*/
576: UPDATE cst_item_cost_details A
577: SET (
578: usage_rate_or_amount

Line 597: FROM cst_open_pos_temp COPT

593: , I_REQUEST_ID
594: , I_PROGRAM_APPL_ID
595: , I_PROGRAM_ID
596: , SYSDATE
597: FROM cst_open_pos_temp COPT
598: WHERE COPT.inventory_item_id = A.inventory_item_id
599: )
600: WHERE organization_id = I_ORG_ID
601: AND cost_type_id = I_COST_TYPE_ID

Line 615: FROM cst_open_pos_temp

611: AND rollup_source_type = 1 /* User Defined */
612: AND A.inventory_item_id in
613: (
614: SELECT inventory_item_id
615: FROM cst_open_pos_temp
616: )
617: ;
618:
619: fnd_file.put_line(fnd_file.log,'Updated '||to_char(SQL%ROWCOUNT)||' rows in cst_item_cost_details.');