DBA Data[Home] [Help]

APPS.MSC_CL_GMP_UTILITY dependencies on CR_RSRC_DTL

Line 2302: ||' cr_rsrc_dtl'||at_apps_link||' crd, '

2298: ||' DECODE(NVL(goa.sequence_dependent_ind,0),1,0,DECODE(gor.prim_rsrc_ind,1,1,0)) is_nonunique, '
2299: ||' NULL setup_id '
2300: ||' FROM sy_uoms_mst'||at_apps_link||' sou, '
2301: ||' sy_uoms_mst'||at_apps_link||' sou2, '
2302: ||' cr_rsrc_dtl'||at_apps_link||' crd, '
2303: ||' fm_rout_dtl'||at_apps_link||' frd, '
2304: ||' gmd_operations'||at_apps_link||' fom, '
2305: ||' gmd_operation_activities'||at_apps_link||' goa, '
2306: ||' gmd_operation_resources'||at_apps_link||' gor '

Line 2633: ||' FROM cr_rsrc_dtl'||at_apps_link||' acrd, '

2629: ||' pcrd.resource_id, acrd.resource_id, '
2630: ||' acrd.min_capacity, acrd.max_capacity, '
2631: ||' cam.runtime_factor, '
2632: /*prod spec alt*/ ||' nvl(cam.preference,-1), nvl(prod.item_id,-1) '
2633: ||' FROM cr_rsrc_dtl'||at_apps_link||' acrd, '
2634: ||' cr_rsrc_dtl'||at_apps_link||' pcrd, '
2635: ||' cr_ares_mst'||at_apps_link||' cam, '
2636: ||' gmp_altresource_products'||at_apps_link||' prod'
2637: ||' WHERE cam.alternate_resource = acrd.resources '

Line 2634: ||' cr_rsrc_dtl'||at_apps_link||' pcrd, '

2630: ||' acrd.min_capacity, acrd.max_capacity, '
2631: ||' cam.runtime_factor, '
2632: /*prod spec alt*/ ||' nvl(cam.preference,-1), nvl(prod.item_id,-1) '
2633: ||' FROM cr_rsrc_dtl'||at_apps_link||' acrd, '
2634: ||' cr_rsrc_dtl'||at_apps_link||' pcrd, '
2635: ||' cr_ares_mst'||at_apps_link||' cam, '
2636: ||' gmp_altresource_products'||at_apps_link||' prod'
2637: ||' WHERE cam.alternate_resource = acrd.resources '
2638: ||' AND cam.primary_resource = pcrd.resources '

Line 7653: ||' cr_rsrc_dtl'||at_apps_link||' rd, '

7649: ||' count(o.oprn_id) OVER (PARTITION BY rd.orgn_code,s.category_id,rd.resource_id) CNT '
7650: ||' FROM '
7651: ||' ic_whse_mst'||at_apps_link||' iwm, '
7652: ||' sy_orgn_mst'||at_apps_link||' sy, '
7653: ||' cr_rsrc_dtl'||at_apps_link||' rd, '
7654: ||' gmp_sequence_types'||at_apps_link||' s, '
7655: ||' gmd_operation_resources'||at_apps_link||' r, '
7656: ||' gmd_operation_activities'||at_apps_link||' a, '
7657: ||' gmd_operations'||at_apps_link||' o '

Line 7692: ||' cr_rsrc_dtl'||at_apps_link||' acrd, '

7688: ||' acrd.resources alt_resources, '
7689: ||' iwm.mtl_organization_id '
7690: ||' FROM ic_whse_mst'||at_apps_link||' iwm, '
7691: ||' sy_orgn_mst'||at_apps_link||' sy, '
7692: ||' cr_rsrc_dtl'||at_apps_link||' acrd, '
7693: ||' cr_rsrc_dtl'||at_apps_link||' pcrd, '
7694: ||' cr_ares_mst'||at_apps_link||' cam '
7695: ||' WHERE cam.alternate_resource = acrd.resources '
7696: ||' AND cam.primary_resource = pcrd.resources '

Line 7693: ||' cr_rsrc_dtl'||at_apps_link||' pcrd, '

7689: ||' iwm.mtl_organization_id '
7690: ||' FROM ic_whse_mst'||at_apps_link||' iwm, '
7691: ||' sy_orgn_mst'||at_apps_link||' sy, '
7692: ||' cr_rsrc_dtl'||at_apps_link||' acrd, '
7693: ||' cr_rsrc_dtl'||at_apps_link||' pcrd, '
7694: ||' cr_ares_mst'||at_apps_link||' cam '
7695: ||' WHERE cam.alternate_resource = acrd.resources '
7696: ||' AND cam.primary_resource = pcrd.resources '
7697: ||' AND acrd.orgn_code = pcrd.orgn_code '

Line 7726: ||' cr_rsrc_dtl'||at_apps_link||' rd, '

7722: ||' count(o.oprn_id) OVER (PARTITION BY rd.orgn_code,s.category_id,rd.resource_id) CNT '
7723: ||' FROM '
7724: ||' ic_whse_mst'||at_apps_link||' iwm, '
7725: ||' sy_orgn_mst'||at_apps_link||' sy, '
7726: ||' cr_rsrc_dtl'||at_apps_link||' rd, '
7727: ||' gmp_sequence_types'||at_apps_link||' s, '
7728: ||' gmd_operation_resources'||at_apps_link||' r, '
7729: ||' gmd_operation_activities'||at_apps_link||' a, '
7730: ||' gmd_operations'||at_apps_link||' o '

Line 7765: ||' cr_rsrc_dtl'||at_apps_link||' acrd, '

7761: ||' acrd.resources alt_resources, '
7762: ||' iwm.mtl_organization_id '
7763: ||' FROM ic_whse_mst'||at_apps_link||' iwm, '
7764: ||' sy_orgn_mst'||at_apps_link||' sy, '
7765: ||' cr_rsrc_dtl'||at_apps_link||' acrd, '
7766: ||' cr_rsrc_dtl'||at_apps_link||' pcrd, '
7767: ||' cr_ares_mst'||at_apps_link||' cam '
7768: ||' WHERE cam.alternate_resource = acrd.resources '
7769: ||' AND cam.primary_resource = pcrd.resources '

Line 7766: ||' cr_rsrc_dtl'||at_apps_link||' pcrd, '

7762: ||' iwm.mtl_organization_id '
7763: ||' FROM ic_whse_mst'||at_apps_link||' iwm, '
7764: ||' sy_orgn_mst'||at_apps_link||' sy, '
7765: ||' cr_rsrc_dtl'||at_apps_link||' acrd, '
7766: ||' cr_rsrc_dtl'||at_apps_link||' pcrd, '
7767: ||' cr_ares_mst'||at_apps_link||' cam '
7768: ||' WHERE cam.alternate_resource = acrd.resources '
7769: ||' AND cam.primary_resource = pcrd.resources '
7770: ||' AND acrd.orgn_code = sy.orgn_code '

Line 8910: || ' cr_rsrc_dtl'||pdblink||' c'

8906: || ' gme_batch_step_activities'||pdblink||' o,'
8907: || ' gme_batch_step_resources'||pdblink||' gs,'
8908: || ' gme_resource_txns'||pdblink||' t , '
8909: || ' gmp_resource_instances'||pdblink||' gri, '
8910: || ' cr_rsrc_dtl'||pdblink||' c'
8911: || ' WHERE'
8912: || ' h.batch_id = r.batch_id '
8913: || ' AND r.batch_id = o.batch_id'
8914: || ' AND r.batchstep_id = o.batchstep_id'

Line 9006: ||' cr_rsrc_dtl'||pdblink||' crd,'

9002: ||' gbsc.plan_start_date, '
9003: ||' gbsc.plan_cmplt_date'
9004: ||' FROM'
9005: ||' gme_batch_step_charges'||pdblink||' gbsc,'
9006: ||' cr_rsrc_dtl'||pdblink||' crd,'
9007: ||' ic_whse_mst'||pdblink||' iwm,'
9008: ||' gmd_recipe_validity_rules'||pdblink||' v,'
9009: ||' gme_batch_steps'||pdblink||' gbs,'
9010: ||' gme_batch_header'||pdblink||' h'

Line 9069: ||' FROM cr_rsrc_dtl'||pdblink||' acrd, '

9065: ||' pcrd.resource_id, acrd.resource_id, '
9066: ||' acrd.min_capacity, acrd.max_capacity, '
9067: ||' cam.runtime_factor, '
9068: /*prod spec alt*/ ||' nvl(cam.preference,-1), nvl(prod.item_id,-1) '
9069: ||' FROM cr_rsrc_dtl'||pdblink||' acrd, '
9070: ||' cr_rsrc_dtl'||pdblink||' pcrd, '
9071: ||' cr_ares_mst'||pdblink||' cam, '
9072: ||' gmp_altresource_products'||pdblink||' prod'
9073: ||' WHERE cam.alternate_resource = acrd.resources '

Line 9070: ||' cr_rsrc_dtl'||pdblink||' pcrd, '

9066: ||' acrd.min_capacity, acrd.max_capacity, '
9067: ||' cam.runtime_factor, '
9068: /*prod spec alt*/ ||' nvl(cam.preference,-1), nvl(prod.item_id,-1) '
9069: ||' FROM cr_rsrc_dtl'||pdblink||' acrd, '
9070: ||' cr_rsrc_dtl'||pdblink||' pcrd, '
9071: ||' cr_ares_mst'||pdblink||' cam, '
9072: ||' gmp_altresource_products'||pdblink||' prod'
9073: ||' WHERE cam.alternate_resource = acrd.resources '
9074: ||' AND cam.primary_resource = pcrd.resources '

Line 14406: ||' cr_rsrc_dtl crd, '

14402: ||' gbsr.batch_id '
14403: ||' FROM gme_batch_step_resources gbsr, '
14404: ||' gme_resource_txns grt, '
14405: ||' sy_orgn_usr sou, '
14406: ||' cr_rsrc_dtl crd, '
14407: ||' gme_batch_header gbh '
14408: ||' WHERE gbsr.batch_id = grt.doc_id '
14409: ||' AND gbh.batch_id = gbsr.batch_id '
14410: ||' AND gbh.plant_code = crd.orgn_code '

Line 14713: || ' FROM cr_rsrc_dtl'||p_db_link||' r, '

14709: /* If the Resource is scheduled to Instance, then value is Yes else No */
14710: || ' decode(r.schedule_ind,2,1,2), '
14711: /*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
14712: || ' 1 '
14713: || ' FROM cr_rsrc_dtl'||p_db_link||' r, '
14714: || ' cr_rsrc_mst'||p_db_link||' rsm, ' /* B4081551 */
14715: || ' mrp_planning_exception_sets'||p_db_link||' mrp, '
14716: || ' sy_orgn_mst'||p_db_link||' p, '
14717: || ' ic_whse_mst'||p_db_link||' w, '

Line 14782: || ' FROM cr_rsrc_dtl'||p_db_link||' r, '

14778: /* If the Resource is scheduled to Instance, then value is Yes else No */
14779: || ' decode(r.schedule_ind,2,1,2), '
14780: /*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
14781: || ' 1 '
14782: || ' FROM cr_rsrc_dtl'||p_db_link||' r, '
14783: || ' cr_rsrc_mst'||p_db_link||' rsm, ' /* B4081551 */
14784: || ' sy_orgn_mst'||p_db_link||' p, '
14785: || ' ic_whse_mst'||p_db_link||' w, '
14786: -- Bug 6467457 modified as below ||' sy_uoms_mst'||p_db_link||' sou '

Line 14830: || ' cr_rsrc_dtl'||p_db_link||' crd, '

14826: || ' NULL,'
14827: || ' 1,'
14828: || ' :instance_id '
14829: || ' FROM sy_orgn_mst'||p_db_link||' sy, '
14830: || ' cr_rsrc_dtl'||p_db_link||' crd, '
14831: || ' cr_rsrc_mst'||p_db_link||' crm '
14832: || ' WHERE sy.orgn_code = crd.orgn_code '
14833: || ' AND sy.resource_whse_code is NOT NULL '
14834: || ' AND crd.resources = crm.resources '

Line 14882: ||' cr_rsrc_dtl'||p_db_link||' crd, '

14878: ||' gri.eff_end_date, '
14879: ||' 2 '
14880: ||' FROM '
14881: ||' gmp_resource_instances'||p_db_link||' gri, '
14882: ||' cr_rsrc_dtl'||p_db_link||' crd, '
14883: ||' sy_orgn_mst'||p_db_link||' som,'
14884: ||' ic_whse_mst'||p_db_link||' iwm '
14885: ||' WHERE '
14886: ||' gri.resource_id = crd.resource_id '

Line 16065: TYPE rsrc_cnt IS TABLE OF cr_rsrc_dtl.assigned_qty%TYPE;

16061: end_index NUMBER ;
16062: first_in NUMBER ;
16063: last_in NUMBER ;
16064:
16065: TYPE rsrc_cnt IS TABLE OF cr_rsrc_dtl.assigned_qty%TYPE;
16066: resource_count rsrc_cnt ;
16067:
16068: TYPE rsrc_id IS TABLE OF cr_rsrc_dtl.resource_id%TYPE;
16069: resource_id rsrc_id ;

Line 16068: TYPE rsrc_id IS TABLE OF cr_rsrc_dtl.resource_id%TYPE;

16064:
16065: TYPE rsrc_cnt IS TABLE OF cr_rsrc_dtl.assigned_qty%TYPE;
16066: resource_count rsrc_cnt ;
16067:
16068: TYPE rsrc_id IS TABLE OF cr_rsrc_dtl.resource_id%TYPE;
16069: resource_id rsrc_id ;
16070:
16071: TYPE inst_id IS TABLE OF gmp_resource_instances.instance_id%TYPE;
16072: instance_id inst_id ;

Line 16123: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

16119:
16120: /* Interval Cursor gives the all the point of inflections */
16121:
16122: stmt_no := 73;
16123: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
16124: sql_stmt1 := ' SELECT /*+ ALL_ROWS DRIVING_SITE (rt) */ '
16125: || ' decode(rt.interval_date,rt.lead_idate,rt.assigned_qty,decode(rt.rsum,0,rt.assigned_qty,rt.assigned_qty-rt.rsum)) resource_count '
16126: || ' ,rt.resource_id '
16127: || ' ,0 instance_id '

Line 16151: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16147: || ' gri.instance_number, '
16148: || ' un.from_date, '
16149: || ' un.to_date, '
16150: || ' 1 resource_units'
16151: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16152: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
16153: || ' gmp_resource_instances '||p_db_link||' gri '
16154: || ' WHERE crd.resource_id = un.resource_id '
16155: || ' AND crd.resource_id = gri.resource_id '

Line 16175: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

16171: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
16172: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
16173: END IF ;
16174:
16175: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
16176: sql_stmt1 := sql_stmt1
16177: || ' AND nvl(un.instance_id,0) <> 0 '
16178: || ' UNION ALL '
16179: || ' SELECT un.resource_id, '

Line 16184: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16180: || ' gri.instance_number, '
16181: || ' un.from_date, '
16182: || ' un.to_date, '
16183: || ' 1 resource_units'
16184: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16185: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
16186: || ' gmp_resource_instances '||p_db_link||' gri '
16187: || ' WHERE crd.resource_id = un.resource_id '
16188: || ' AND crd.resource_id = gri.resource_id '

Line 16207: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

16203: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
16204: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
16205: END IF ;
16206:
16207: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
16208: sql_stmt1 := sql_stmt1
16209: || ' AND gri.instance_number in '
16210: || ' ( select tgri.instance_number '
16211: || ' FROM gmp_resource_instances '||p_db_link||' tgri '

Line 16221: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16217: || ' 0 instance_number, '
16218: || ' un.from_date, '
16219: || ' un.to_date, '
16220: || ' un.resource_units '
16221: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16222: || ' gmp_rsrc_unavail_dtl_v ' ||p_db_link||' un'
16223: || ' WHERE crd.resource_id = un.resource_id '
16224: || ' AND crd.orgn_code = :orgn_code2 ' ;
16225:

Line 16240: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

16236: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
16237: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
16238: END IF ;
16239:
16240: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
16241: sql_stmt1 := sql_stmt1
16242: || ' AND NOT EXISTS '
16243: || ' (SELECT 1 '
16244: || ' FROM gmp_resource_instances ' ||p_db_link||' gri '

Line 16276: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16272: || ' 0 shift_num,'
16273: || ' 0 resource_count,'
16274: || ' un.from_date interval_date, '
16275: || ' crd.assigned_qty assigned_qty '
16276: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16277: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
16278: || ' gmp_resource_instances '||p_db_link||' gri '
16279: || ' WHERE crd.resource_id = un.resource_id '
16280: || ' AND crd.resource_id = gri.resource_id '

Line 16301: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

16297: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
16298: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
16299: END IF ;
16300:
16301: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
16302: sql_stmt1 := sql_stmt1
16303: || ' UNION ALL '
16304: || ' SELECT un.resource_id resource_id, '
16305: || ' gri.instance_number instance_number,'

Line 16310: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16306: || ' 0 shift_num,'
16307: || ' 0 resource_count,'
16308: || ' un.to_date interval_date, '
16309: || ' crd.assigned_qty assigned_qty '
16310: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16311: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
16312: || ' gmp_resource_instances '||p_db_link||' gri '
16313: || ' WHERE crd.resource_id = un.resource_id '
16314: || ' AND crd.resource_id = gri.resource_id '

Line 16335: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

16331: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
16332: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
16333: END IF ;
16334:
16335: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
16336: sql_stmt1 := sql_stmt1
16337: || ' UNION ALL '
16338: || ' SELECT un.resource_id resource_id, '
16339: || ' gri.instance_number instance_number,'

Line 16344: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16340: || ' 0 shift_num,'
16341: || ' 0 resource_count,'
16342: || ' un.from_date interval_date, '
16343: || ' crd.assigned_qty assigned_qty '
16344: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16345: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
16346: || ' gmp_resource_instances '||p_db_link||' gri '
16347: || ' WHERE crd.resource_id = un.resource_id '
16348: || ' AND crd.resource_id = gri.resource_id '

Line 16368: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

16364: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
16365: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
16366: END IF ;
16367:
16368: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
16369: sql_stmt1 := sql_stmt1
16370: || ' AND gri.instance_number in '
16371: || ' ( select tgri.instance_number '
16372: || ' FROM gmp_resource_instances '||p_db_link||' tgri '

Line 16383: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16379: || ' 0 shift_num,'
16380: || ' 0 resource_count,'
16381: || ' un.to_date interval_date, '
16382: || ' crd.assigned_qty assigned_qty '
16383: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16384: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
16385: || ' gmp_resource_instances '||p_db_link||' gri '
16386: || ' WHERE crd.resource_id = un.resource_id '
16387: || ' AND crd.resource_id = gri.resource_id '

Line 16421: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16417: || ' 0 shift_num,'
16418: || ' 0 resource_count,'
16419: || ' un.from_date interval_date, '
16420: || ' crd.assigned_qty assigned_qty '
16421: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16422: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un '
16423: || ' WHERE crd.resource_id = un.resource_id '
16424: || ' AND crd.orgn_code = :orgn_code44 ' ;
16425:

Line 16441: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

16437: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
16438: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
16439: END IF ;
16440:
16441: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
16442: sql_stmt1 := sql_stmt1
16443: || ' AND NOT EXISTS '
16444: || ' (SELECT 1 '
16445: || ' FROM gmp_resource_instances '||p_db_link||' gri '

Line 16454: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16450: || ' 0 shift_num,'
16451: || ' 0 resource_count,'
16452: || ' un.to_date interval_date, '
16453: || ' crd.assigned_qty assigned_qty '
16454: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16455: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un '
16456: || ' WHERE crd.resource_id = un.resource_id '
16457: || ' AND crd.orgn_code = :orgn_code444 ' ;
16458:

Line 16491: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16487: || ' exp.shift_num, '
16488: || ' 0 , '
16489: || ' exp.from_date interval_date, '
16490: || ' crd.assigned_qty assigned_qty '
16491: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16492: || ' gmp_calendar_detail_gtmp ' ||p_db_link||' exp '
16493: || ' WHERE crd.orgn_code = :orgn_code5 ' ;
16494:
16495: IF (p_usage = 'BASED') THEN /* Usage APS */

Line 16510: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

16506: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
16507: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
16508: END IF ;
16509:
16510: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
16511: sql_stmt1 := sql_stmt1
16512: || ' AND exp.calendar_id = :curr_cal2 '
16513: || ' UNION ALL '
16514: || ' SELECT crd.resource_id , '

Line 16520: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

16516: || ' exp.shift_num, '
16517: || ' 0 , '
16518: || ' exp.to_date interval_date, '
16519: || ' crd.assigned_qty assigned_qty '
16520: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
16521: || ' gmp_calendar_detail_gtmp ' ||p_db_link||' exp '
16522: || ' WHERE crd.orgn_code = :orgn_code6 ' ;
16523:
16524: IF (p_usage = 'BASED') THEN /* Usage APS */

Line 16753: || ' cr_rsrc_dtl'||p_db_link||' crd '

16749: || ' :instance_id, '
16750: || ' crd.assigned_qty '
16751: || ' FROM gmp_calendar_detail_gtmp'||p_db_link||' gtmp, '
16752: || ' sy_orgn_mst'||p_db_link||' som, '
16753: || ' cr_rsrc_dtl'||p_db_link||' crd '
16754: || ' WHERE gtmp.calendar_id = :curr_cal_id '
16755: || ' AND NVL(crd.calendar_id,som.mfg_calendar_id)=gtmp.calendar_id '
16756: || ' AND som.orgn_code = crd.orgn_code '
16757: || ' AND som.resource_whse_code IS NOT NULL '

Line 17700: ||' cr_rsrc_dtl'||p_db_link||' crd, '

17696: ||' shcl.calendar_no, shcl.calendar_desc, '
17697: ||' sy.orgn_code, iwm.mtl_organization_id '
17698: ||' FROM mr_shcl_hdr'||p_db_link||' shcl, '
17699: ||' sy_orgn_mst'||p_db_link||' sy, '
17700: ||' cr_rsrc_dtl'||p_db_link||' crd, '
17701: ||' ic_whse_mst'||p_db_link||' iwm '
17702: ||' WHERE sy.orgn_code = crd.orgn_code '
17703: ||' AND sy.mfg_calendar_id <> crd.calendar_id '
17704: ||' AND crd.calendar_id IS NOT NULL '

Line 17871: ||' cr_rsrc_dtl r '

17867: ||' DISTINCT NVL(r.calendar_id,sy.mfg_calendar_id), '
17868: ||' shcl.calendar_no, shcl.calendar_desc '
17869: ||' FROM mr_shcl_hdr shcl, '
17870: ||' sy_orgn_mst sy, '
17871: ||' cr_rsrc_dtl r '
17872: ||' WHERE sy.orgn_code = r.orgn_code '
17873: ||' AND NVL(r.calendar_id,sy.mfg_calendar_id)=shcl.calendar_id '
17874: ||' AND r.orgn_code = :lorgn_code '
17875: ||' AND shcl.delete_mark = 0 ' ;

Line 17920: ||' FROM cr_rsrc_dtl '

17916: ' AND plant_code = :Plant_code1 ';
17917:
17918: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
17919: delete_stmt := delete_stmt ||' AND resource_id in (select resource_id '
17920: ||' FROM cr_rsrc_dtl '
17921: ||' WHERE orgn_code = :Plant_code2 '
17922: ||' AND resources BETWEEN :frsrc and :trsrc ) ';
17923: EXECUTE IMMEDIATE delete_stmt USING l_calendar_id, p_orgn_code,
17924: p_orgn_code, v_from_rsrc, v_to_rsrc;

Line 17927: ||' FROM cr_rsrc_dtl '

17923: EXECUTE IMMEDIATE delete_stmt USING l_calendar_id, p_orgn_code,
17924: p_orgn_code, v_from_rsrc, v_to_rsrc;
17925: ELSIF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NULL) THEN
17926: delete_stmt := delete_stmt ||' AND resource_id in (select resource_id '
17927: ||' FROM cr_rsrc_dtl '
17928: ||' WHERE orgn_code = :Plant_code2 '
17929: ||' AND resources > :frsrc ) ';
17930: EXECUTE IMMEDIATE delete_stmt USING l_calendar_id, p_orgn_code,
17931: p_orgn_code, v_from_rsrc;

Line 18096: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

18092: /* Interval Cursor gives the all the point of inflections */
18093:
18094: stmt_no := 63;
18095: /* 03/26/02 Rajesh Patangya B2282409, Filter extra resource information */
18096: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
18097: sql_stmt1 := ' SELECT /*+ ALL_ROWS */ '
18098: || ' decode(rt.interval_date,rt.lead_idate,rt.assigned_qty,'
18099: || ' (rt.assigned_qty-nvl(rt.rsum,0))) resource_count '
18100: || ' ,rt.resource_id '

Line 18129: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

18125: || ' gri.instance_id, '
18126: || ' un.from_date, '
18127: || ' un.to_date, '
18128: || ' 1 resource_units'
18129: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
18130: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
18131: || ' gmp_resource_instances '||p_db_link||' gri '
18132: || ' WHERE crd.resource_id = un.resource_id '
18133: || ' AND crd.resource_id = gri.resource_id '

Line 18154: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

18150: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
18151: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc1 and :trsrc2 ' ;
18152: END IF ;
18153:
18154: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
18155: sql_stmt1 := sql_stmt1
18156: || ' UNION ALL '
18157: || ' SELECT un.resource_id, '
18158: || ' gri.instance_id, '

Line 18162: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

18158: || ' gri.instance_id, '
18159: || ' un.from_date, '
18160: || ' un.to_date, '
18161: || ' 1 resource_units'
18162: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
18163: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
18164: || ' gmp_resource_instances '||p_db_link||' gri '
18165: || ' WHERE crd.resource_id = un.resource_id '
18166: || ' AND crd.resource_id = gri.resource_id '

Line 18185: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

18181:
18182: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
18183: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc2 and :trsrc2 ' ;
18184: END IF ;
18185: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
18186: sql_stmt1 := sql_stmt1
18187: || ' AND gri.instance_number in '
18188: || ' ( select tgri.instance_number '
18189: || ' FROM gmp_resource_instances '||p_db_link||' tgri '

Line 18224: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

18220: || ' gri.instance_id instance_id,'
18221: || ' 0 shift_num,'
18222: || ' 1 resource_count,'
18223: || ' un.from_date interval_date '
18224: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
18225: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
18226: || ' gmp_resource_instances '||p_db_link||' gri '
18227: || ' WHERE crd.resource_id = un.resource_id '
18228: || ' AND crd.resource_id = gri.resource_id '

Line 18249: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

18245: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
18246: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc3 and :trsrc3 ' ;
18247: END IF ;
18248:
18249: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
18250: sql_stmt1 := sql_stmt1
18251: || ' UNION ALL '
18252: || ' SELECT un.resource_id resource_id, '
18253: || ' gri.instance_id instance_id,'

Line 18257: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

18253: || ' gri.instance_id instance_id,'
18254: || ' 0 shift_num,'
18255: || ' 1 resource_count,'
18256: || ' un.to_date interval_date '
18257: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
18258: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
18259: || ' gmp_resource_instances '||p_db_link||' gri '
18260: || ' WHERE crd.resource_id = un.resource_id '
18261: || ' AND crd.resource_id = gri.resource_id '

Line 18281: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

18277: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
18278: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc4 and :trsrc4 ' ;
18279: END IF ;
18280:
18281: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
18282: sql_stmt1 := sql_stmt1
18283: || ' UNION ALL '
18284: || ' SELECT un.resource_id resource_id, '
18285: || ' gri.instance_id instance_id,'

Line 18289: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

18285: || ' gri.instance_id instance_id,'
18286: || ' 0 shift_num,'
18287: || ' 1 resource_count,'
18288: || ' un.from_date interval_date '
18289: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
18290: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
18291: || ' gmp_resource_instances '||p_db_link||' gri '
18292: || ' WHERE crd.resource_id = un.resource_id '
18293: || ' AND crd.resource_id = gri.resource_id '

Line 18312: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

18308: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
18309: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc5 and :trsrc5 ' ;
18310: END IF ;
18311:
18312: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
18313: sql_stmt1 := sql_stmt1
18314: || ' AND gri.instance_number in '
18315: || ' ( select tgri.instance_number '
18316: || ' FROM gmp_resource_instances '||p_db_link||' tgri '

Line 18326: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

18322: || ' gri.instance_id instance_id,'
18323: || ' 0 shift_num,'
18324: || ' 1 resource_count,'
18325: || ' un.to_date interval_date '
18326: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
18327: || ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
18328: || ' gmp_resource_instances '||p_db_link||' gri '
18329: || ' WHERE crd.resource_id = un.resource_id '
18330: || ' AND crd.resource_id = gri.resource_id '

Line 18350: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

18346: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
18347: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc6 and :trsrc6 ' ;
18348: END IF ;
18349:
18350: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
18351: sql_stmt1 := sql_stmt1
18352: || ' AND gri.instance_number in '
18353: || ' ( select tgri.instance_number '
18354: || ' FROM gmp_resource_instances '||p_db_link||' tgri '

Line 18370: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

18366: || ' exp.shift_num, '
18367: || ' 1 , '
18368: || ' (exp.shift_date + '
18369: || ' (exp.from_time/86400)) interval_date '
18370: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
18371: || ' gmp_calendar_detail_gtmp ' ||p_db_link||' exp, '
18372: || ' gmp_resource_instances '||p_db_link||' gri '
18373: || ' WHERE crd.orgn_code = :orgn_code7 ' ;
18374:

Line 18393: -- HW B4309093 Check for calendar id in cr_rsrc_dtl

18389: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
18390: sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc7 and :trsrc7 ' ;
18391: END IF ;
18392:
18393: -- HW B4309093 Check for calendar id in cr_rsrc_dtl
18394: sql_stmt1 := sql_stmt1
18395: || ' UNION ALL '
18396: || ' SELECT crd.resource_id , '
18397: || ' gri.instance_id, '

Line 18402: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '

18398: || ' exp.shift_num, '
18399: || ' 1 , '
18400: || ' (exp.shift_date + '
18401: || ' (exp.to_time/86400)) interval_date '
18402: || ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
18403: || ' gmp_calendar_detail_gtmp ' ||p_db_link||' exp, '
18404: || ' gmp_resource_instances '||p_db_link||' gri '
18405: || ' WHERE crd.orgn_code = :orgn_code8 ' ;
18406:

Line 18734: ||' from cr_rsrc_dtl '||p_db_link

18730:
18731: IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
18732:
18733: sql_stmt2 := sql_stmt2 ||' AND resource_id in (select resource_id '
18734: ||' from cr_rsrc_dtl '||p_db_link
18735: ||' WHERE orgn_code = :orgn_code2 '
18736: ||' AND resources BETWEEN :frsrc and :trsrc )' ;
18737: END IF ;
18738: