eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.DDR_WEBSERVICES_PUB dependencies on DDR_WEBSERVICES_CONSTANTS

Line 502: UPDATE DDR_WS_JOB set status= ddr_webservices_constants.g_ret_sts_running,start_date=sysdate where job_id=p_job_id ;

498: l_job_id NUMBER:=null;
499: BEGIN
500: l_job_id:=p_job_id;
501: --update job status to Running
502: UPDATE DDR_WS_JOB set status= ddr_webservices_constants.g_ret_sts_running,start_date=sysdate where job_id=p_job_id ;
503: --build dynamic query
504: get_dyn_query(p_api_version,
505: 'A',
506: p_mfg_org_cd,

Line 525: IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

521: l_return_status,
522: l_msg_count,
523: l_msg_data,
524: l_query);
525: IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
526: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
527: RETURN;
528: END IF;
529: --write data to xml file based on dynamic query generated

Line 532: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;

528: END IF;
529: --write data to xml file based on dynamic query generated
530: write_fact_to_xml_file(l_query,p_fact_code,l_job_id,l_return_status,l_msg_count,l_msg_data);
531: --update job status to complete/error
532: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
533: EXCEPTION
534: WHEN NO_DATA_FOUND THEN
535: l_return_status:=ddr_webservices_constants.g_ret_sts_error;
536: l_msg_count:=1;

Line 535: l_return_status:=ddr_webservices_constants.g_ret_sts_error;

531: --update job status to complete/error
532: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
533: EXCEPTION
534: WHEN NO_DATA_FOUND THEN
535: l_return_status:=ddr_webservices_constants.g_ret_sts_error;
536: l_msg_count:=1;
537: l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
538: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
539: WHEN OTHERS THEN

Line 540: l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

536: l_msg_count:=1;
537: l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
538: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
539: WHEN OTHERS THEN
540: l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
541: l_msg_count:=1;
542: l_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
543: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
544: END ddr_fact_aggr_prc;

Line 573: UPDATE DDR_WS_JOB set status= ddr_webservices_constants.g_ret_sts_running,start_date=sysdate where job_id=p_job_id ;

569: l_job_id NUMBER:=null;
570: BEGIN
571: l_job_id:=p_job_id;
572: --update job status to Running
573: UPDATE DDR_WS_JOB set status= ddr_webservices_constants.g_ret_sts_running,start_date=sysdate where job_id=p_job_id ;
574: --build dynamic query
575: get_dyn_query(p_api_version,
576: 'D',
577: p_mfg_org_cd,

Line 596: IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

592: l_return_status,
593: l_msg_count,
594: l_msg_data,
595: l_query);
596: IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
597: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
598: RETURN;
599: END IF;
600: --write data to xml file based on dynamic query generated

Line 603: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;

599: END IF;
600: --write data to xml file based on dynamic query generated
601: write_fact_to_xml_file(l_query,p_fact_code,l_job_id,l_return_status,l_msg_count,l_msg_data);
602: --update job status to complete/error
603: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
604: EXCEPTION
605: WHEN NO_DATA_FOUND THEN
606: l_return_status:=ddr_webservices_constants.g_ret_sts_error;
607: l_msg_count:=1;

Line 606: l_return_status:=ddr_webservices_constants.g_ret_sts_error;

602: --update job status to complete/error
603: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
604: EXCEPTION
605: WHEN NO_DATA_FOUND THEN
606: l_return_status:=ddr_webservices_constants.g_ret_sts_error;
607: l_msg_count:=1;
608: l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
609: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
610: WHEN OTHERS THEN

Line 611: l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

607: l_msg_count:=1;
608: l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
609: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
610: WHEN OTHERS THEN
611: l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
612: l_msg_count:=1;
613: l_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
614: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
615: END ddr_fact_details_prc;

Line 681: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

677: p_fact_code,
678: x_return_status,
679: x_msg_count,
680: x_msg_data);
681: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
682: RETURN;
683: END IF;
684: -- get fact table name
685: get_fact_table(p_fact_code,x_return_status,x_msg_count,x_msg_data,l_fact_table_name);

Line 705: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

701: x_msg_data,
702: l_itm_ref_tbls,
703: l_itm_ref_joins,
704: l_itm_where_clus);
705: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
706: RETURN;
707: END IF;
708: END IF;
709: --get the reference tables ,reference table joins and where clause join for the organization hierarchy level

Line 721: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

717: x_msg_data,
718: l_org_ref_tbls,
719: l_org_ref_joins,
720: l_org_where_clus);
721: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
722: RETURN;
723: END IF;
724: END IF;
725: --get the reference tables ,reference table joins and where clause join for the time hierarchy level

Line 738: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

734: x_msg_data,
735: l_time_ref_tbls,
736: l_time_ref_joins,
737: l_time_where_clus);
738: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
739: RETURN;
740: END IF;
741: END IF;
742: --get the reference tables ,reference table joins and where clause join for the location hierarchy level

Line 755: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

751: x_msg_data,
752: l_loc_ref_tbls,
753: l_loc_ref_joins,
754: l_loc_where_clus);
755: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
756: RETURN;
757: END IF;
758: END IF;
759: --Bug 6880404 change start

Line 777: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

773: x_return_status,
774: x_msg_count,
775: x_msg_data,
776: l_group_col);
777: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
778: RETURN;
779: END IF;
780: END IF;
781:

Line 836: x_return_status := ddr_webservices_constants.g_ret_sts_error;

832: -- DBMS_OUTPUT.PUT_LINE('l_query='||l_query);
833: x_dyn_query:= l_query;
834: EXCEPTION
835: WHEN NO_DATA_FOUND THEN
836: x_return_status := ddr_webservices_constants.g_ret_sts_error;
837: x_msg_count := 1;
838: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
839: WHEN OTHERS THEN
840: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 840: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

836: x_return_status := ddr_webservices_constants.g_ret_sts_error;
837: x_msg_count := 1;
838: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
839: WHEN OTHERS THEN
840: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
841: x_msg_count:=1;
842: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
843: END get_dyn_query;
844:

Line 855: WHEN ddr_webservices_constants.g_misd_cd THEN

851: BEGIN
852: --case condition to determine name of the fact table
853: CASE p_fact_code
854: -- for MARKET ITEM SALES DAY
855: WHEN ddr_webservices_constants.g_misd_cd THEN
856: x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;
857: -- for PROMOTION PLAN
858: WHEN ddr_webservices_constants.g_pp_cd THEN
859: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;

Line 856: x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;

852: --case condition to determine name of the fact table
853: CASE p_fact_code
854: -- for MARKET ITEM SALES DAY
855: WHEN ddr_webservices_constants.g_misd_cd THEN
856: x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;
857: -- for PROMOTION PLAN
858: WHEN ddr_webservices_constants.g_pp_cd THEN
859: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
860: -- for RETAIL INVENTORY ITEM DAY

Line 858: WHEN ddr_webservices_constants.g_pp_cd THEN

854: -- for MARKET ITEM SALES DAY
855: WHEN ddr_webservices_constants.g_misd_cd THEN
856: x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;
857: -- for PROMOTION PLAN
858: WHEN ddr_webservices_constants.g_pp_cd THEN
859: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
860: -- for RETAIL INVENTORY ITEM DAY
861: WHEN ddr_webservices_constants.g_riid_cd THEN
862: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;

Line 859: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;

855: WHEN ddr_webservices_constants.g_misd_cd THEN
856: x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;
857: -- for PROMOTION PLAN
858: WHEN ddr_webservices_constants.g_pp_cd THEN
859: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
860: -- for RETAIL INVENTORY ITEM DAY
861: WHEN ddr_webservices_constants.g_riid_cd THEN
862: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
863: -- for RETAIL SALE RETURN ITEM DAY fact

Line 861: WHEN ddr_webservices_constants.g_riid_cd THEN

857: -- for PROMOTION PLAN
858: WHEN ddr_webservices_constants.g_pp_cd THEN
859: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
860: -- for RETAIL INVENTORY ITEM DAY
861: WHEN ddr_webservices_constants.g_riid_cd THEN
862: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
863: -- for RETAIL SALE RETURN ITEM DAY fact
864: WHEN ddr_webservices_constants.g_rsrid_cd THEN
865: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;

Line 862: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;

858: WHEN ddr_webservices_constants.g_pp_cd THEN
859: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
860: -- for RETAIL INVENTORY ITEM DAY
861: WHEN ddr_webservices_constants.g_riid_cd THEN
862: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
863: -- for RETAIL SALE RETURN ITEM DAY fact
864: WHEN ddr_webservices_constants.g_rsrid_cd THEN
865: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
866: -- for RETAILER ORDER ITEM DAY

Line 864: WHEN ddr_webservices_constants.g_rsrid_cd THEN

860: -- for RETAIL INVENTORY ITEM DAY
861: WHEN ddr_webservices_constants.g_riid_cd THEN
862: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
863: -- for RETAIL SALE RETURN ITEM DAY fact
864: WHEN ddr_webservices_constants.g_rsrid_cd THEN
865: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
866: -- for RETAILER ORDER ITEM DAY
867: WHEN ddr_webservices_constants.g_roid_cd THEN
868: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;

Line 865: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;

861: WHEN ddr_webservices_constants.g_riid_cd THEN
862: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
863: -- for RETAIL SALE RETURN ITEM DAY fact
864: WHEN ddr_webservices_constants.g_rsrid_cd THEN
865: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
866: -- for RETAILER ORDER ITEM DAY
867: WHEN ddr_webservices_constants.g_roid_cd THEN
868: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
869: -- for RETAILER SHIP ITEM DAY

Line 867: WHEN ddr_webservices_constants.g_roid_cd THEN

863: -- for RETAIL SALE RETURN ITEM DAY fact
864: WHEN ddr_webservices_constants.g_rsrid_cd THEN
865: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
866: -- for RETAILER ORDER ITEM DAY
867: WHEN ddr_webservices_constants.g_roid_cd THEN
868: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
869: -- for RETAILER SHIP ITEM DAY
870: WHEN ddr_webservices_constants.g_rsid_cd THEN
871: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;

Line 868: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;

864: WHEN ddr_webservices_constants.g_rsrid_cd THEN
865: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
866: -- for RETAILER ORDER ITEM DAY
867: WHEN ddr_webservices_constants.g_roid_cd THEN
868: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
869: -- for RETAILER SHIP ITEM DAY
870: WHEN ddr_webservices_constants.g_rsid_cd THEN
871: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
872: -- for SALE FORECAST ITEM BY DAY

Line 870: WHEN ddr_webservices_constants.g_rsid_cd THEN

866: -- for RETAILER ORDER ITEM DAY
867: WHEN ddr_webservices_constants.g_roid_cd THEN
868: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
869: -- for RETAILER SHIP ITEM DAY
870: WHEN ddr_webservices_constants.g_rsid_cd THEN
871: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
872: -- for SALE FORECAST ITEM BY DAY
873: WHEN ddr_webservices_constants.g_sfid_cd THEN
874: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;

Line 871: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;

867: WHEN ddr_webservices_constants.g_roid_cd THEN
868: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
869: -- for RETAILER SHIP ITEM DAY
870: WHEN ddr_webservices_constants.g_rsid_cd THEN
871: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
872: -- for SALE FORECAST ITEM BY DAY
873: WHEN ddr_webservices_constants.g_sfid_cd THEN
874: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;
875: END CASE;

Line 873: WHEN ddr_webservices_constants.g_sfid_cd THEN

869: -- for RETAILER SHIP ITEM DAY
870: WHEN ddr_webservices_constants.g_rsid_cd THEN
871: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
872: -- for SALE FORECAST ITEM BY DAY
873: WHEN ddr_webservices_constants.g_sfid_cd THEN
874: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;
875: END CASE;
876: EXCEPTION
877: WHEN OTHERS THEN

Line 874: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;

870: WHEN ddr_webservices_constants.g_rsid_cd THEN
871: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
872: -- for SALE FORECAST ITEM BY DAY
873: WHEN ddr_webservices_constants.g_sfid_cd THEN
874: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;
875: END CASE;
876: EXCEPTION
877: WHEN OTHERS THEN
878: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 878: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

874: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;
875: END CASE;
876: EXCEPTION
877: WHEN OTHERS THEN
878: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
879: x_msg_count:=1;
880: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
881: END get_fact_table;
882:

Line 893: WHEN ddr_webservices_constants.g_misd_cd THEN

889: BEGIN
890: --case condition to identify aggregate column names from fact table
891: CASE p_fact_code
892: -- for MARKET ITEM SALES DAY
893: WHEN ddr_webservices_constants.g_misd_cd THEN
894: x_fact_cols:='sum(x.AVG_MMACV_SLS_RATE) AVG_MMACV_SLS_RATE,'
895: ||'sum(x.AVG_STORE_SELL_ITEM_QTY) AVG_STORE_SELL_ITEM_QTY,'
896: ||'sum(x.AVG_ACV_WGT_DSTRBTN_PCT) AVG_ACV_WGT_DSTRBTN_PCT,'
897: ||'sum(x.AVG_WGT_PRICE_RDCTN_PCT) AVG_WGT_PRICE_RDCTN_PCT,'

Line 907: WHEN ddr_webservices_constants.g_pp_cd THEN

903: ||'sum(x.SLS_PRICE_CUT_AMT) SLS_PRICE_CUT_AMT,'
904: ||'sum(x.MAIN_AD_QTY) MAIN_AD_QTY,'
905: ||'sum(x.MAIN_AD_AMT) MAIN_AD_AMT';
906: --for PROMOTION PLAN
907: WHEN ddr_webservices_constants.g_pp_cd THEN
908: x_fact_cols:='sum(x.PRMTN_PRICE_AMT) PRMTN_PRICE_AMT';
909: -- for RETAIL INVENTORY ITEM DAY
910: WHEN ddr_webservices_constants.g_riid_cd THEN
911: x_fact_cols:= 'sum(x.ON_HAND_QTY) ON_HAND_QTY,'

Line 910: WHEN ddr_webservices_constants.g_riid_cd THEN

906: --for PROMOTION PLAN
907: WHEN ddr_webservices_constants.g_pp_cd THEN
908: x_fact_cols:='sum(x.PRMTN_PRICE_AMT) PRMTN_PRICE_AMT';
909: -- for RETAIL INVENTORY ITEM DAY
910: WHEN ddr_webservices_constants.g_riid_cd THEN
911: x_fact_cols:= 'sum(x.ON_HAND_QTY) ON_HAND_QTY,'
912: ||'sum(x.RECVD_QTY) RECVD_QTY,'
913: ||'sum(x.IN_TRANSIT_QTY) IN_TRANSIT_QTY,'
914: ||'sum(x.BCK_ORDR_QTY) BCK_ORDR_QTY,'

Line 927: WHEN ddr_webservices_constants.g_rsrid_cd THEN

923: ||'sum(x.IN_TRANSIT_RTL_AMT) IN_TRANSIT_RTL_AMT,'
924: ||'sum(x.BCKORDR_RTL_AMT) BCKORDR_RTL_AMT,'
925: ||'sum(x.QLTY_HOLD_RTL_AMT) QLTY_HOLD_RTL_AMT';
926: -- for RETAIL SALE RETURN ITEM DAY fact
927: WHEN ddr_webservices_constants.g_rsrid_cd THEN
928: x_fact_cols:='sum(x.SLS_QTY) SLS_QTY,'
929: ||'sum(x.SLS_AMT) SLS_AMT,'
930: ||'sum(x.SLS_COST_AMT) SLS_COST_AMT,'
931: ||'sum(x.RTRN_QTY) RTRN_QTY,'

Line 935: WHEN ddr_webservices_constants.g_roid_cd THEN

931: ||'sum(x.RTRN_QTY) RTRN_QTY,'
932: ||'sum(x.RTRN_AMT) RTRN_AMT,'
933: ||'sum(x.RTRN_COST_AMT) RTRN_COST_AMT';
934: -- for RETAILER ORDER ITEM DAY
935: WHEN ddr_webservices_constants.g_roid_cd THEN
936: x_fact_cols:='sum(x.ORDR_QTY) ORDR_QTY,'
937: ||'sum(x.ORDR_AMT) ORDR_AMT';
938: -- for RETAILER SHIP ITEM DAY
939: WHEN ddr_webservices_constants.g_rsid_cd THEN

Line 939: WHEN ddr_webservices_constants.g_rsid_cd THEN

935: WHEN ddr_webservices_constants.g_roid_cd THEN
936: x_fact_cols:='sum(x.ORDR_QTY) ORDR_QTY,'
937: ||'sum(x.ORDR_AMT) ORDR_AMT';
938: -- for RETAILER SHIP ITEM DAY
939: WHEN ddr_webservices_constants.g_rsid_cd THEN
940: x_fact_cols:= 'sum(x.SHIP_QTY) SHIP_QTY,'
941: ||'sum(x.SHIP_AMT) SHIP_AMT';
942: -- for SALE FORECAST ITEM BY DAY
943: WHEN ddr_webservices_constants.g_sfid_cd THEN

Line 943: WHEN ddr_webservices_constants.g_sfid_cd THEN

939: WHEN ddr_webservices_constants.g_rsid_cd THEN
940: x_fact_cols:= 'sum(x.SHIP_QTY) SHIP_QTY,'
941: ||'sum(x.SHIP_AMT) SHIP_AMT';
942: -- for SALE FORECAST ITEM BY DAY
943: WHEN ddr_webservices_constants.g_sfid_cd THEN
944: x_fact_cols:= 'sum(x.FRCST_SLS_QTY) FRCST_SLS_QTY,'
945: ||'sum(x.FRCST_SLS_AMT) FRCST_SLS_AMT';
946: END CASE;
947: EXCEPTION

Line 949: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

945: ||'sum(x.FRCST_SLS_AMT) FRCST_SLS_AMT';
946: END CASE;
947: EXCEPTION
948: WHEN OTHERS THEN
949: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
950: x_msg_count:=1;
951: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
952: END get_aggr_fact_colms;
953:

Line 964: WHEN ddr_webservices_constants.g_misd_cd THEN

960: BEGIN
961: --case condition to identify aggregate column names from fact table
962: CASE p_fact_code
963: -- for MARKET ITEM SALES DAY
964: WHEN ddr_webservices_constants.g_misd_cd THEN
965: x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
966: ||'x.RTL_ORG_CD RTL_ORG_CD,'
967: ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
968: ||'x.MKT_AREA_ID MKT_AREA_ID,'

Line 1010: WHEN ddr_webservices_constants.g_pp_cd THEN

1006: ||'x.MAIN_AD_AMT MAIN_AD_AMT,'
1007: ||'x.MAIN_AD_AMT_LCL MAIN_AD_AMT_LCL,'
1008: ||'x.MAIN_AD_AMT_RPT MAIN_AD_AMT_RPT';
1009: --for PROMOTION PLAN
1010: WHEN ddr_webservices_constants.g_pp_cd THEN
1011: x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
1012: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1013: ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1014: ||'x.RTL_BSNS_UNIT_CD RTL_BSNS_UNIT_CD,'

Line 1027: WHEN ddr_webservices_constants.g_riid_cd THEN

1023: ||'x.PRMTN_PRICE_AMT PRMTN_PRICE_AMT,'
1024: ||'x.PRMTN_PRICE_AMT_LCL PRMTN_PRICE_AMT_LCL,'
1025: ||'x.PRMTN_PRICE_AMT_RPT PRMTN_PRICE_AMT_RPT';
1026: -- for RETAIL INVENTORY ITEM DAY
1027: WHEN ddr_webservices_constants.g_riid_cd THEN
1028: x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1029: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1030: ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1031: ||'x.RTL_BSNS_UNIT_CD RTL_BSNS_UNIT_CD,'

Line 1090: WHEN ddr_webservices_constants.g_rsrid_cd THEN

1086: ||'x.QLTY_HOLD_RTL_AMT QLTY_HOLD_RTL_AMT,'
1087: ||'x.QLTY_HOLD_RTL_AMT_LCL QLTY_HOLD_RTL_AMT_LCL,'
1088: ||'x.QLTY_HOLD_RTL_AMT_RPT QLTY_HOLD_RTL_AMT_RPT';
1089: -- for RETAIL SALE RETURN ITEM DAY fact
1090: WHEN ddr_webservices_constants.g_rsrid_cd THEN
1091: x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
1092: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1093: ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1094: ||'x.BSNS_UNIT_CD BSNS_UNIT_CD,'

Line 1123: WHEN ddr_webservices_constants.g_roid_cd THEN

1119: ||'x.RTRN_COST_AMT RTRN_COST_AMT,'
1120: ||'x.RTRN_COST_AMT_LCL RTRN_COST_AMT_LCL,'
1121: ||'x.RTRN_COST_AMT_RPT RTRN_COST_AMT_RPT';
1122: -- for RETAILER ORDER ITEM DAY
1123: WHEN ddr_webservices_constants.g_roid_cd THEN
1124: x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
1125: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1126: ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1127: ||'x.RTL_BSNS_UNIT_CD RTL_BSNS_UNIT_CD,'

Line 1144: WHEN ddr_webservices_constants.g_rsid_cd THEN

1140: ||'x.ORDR_AMT ORDR_AMT,'
1141: ||'x.ORDR_AMT_LCL ORDR_AMT_LCL,'
1142: ||'x.ORDR_AMT_RPT ORDR_AMT_RPT';
1143: -- for RETAILER SHIP ITEM DAY
1144: WHEN ddr_webservices_constants.g_rsid_cd THEN
1145: x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1146: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1147: ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1148: ||'x.RTL_BSNS_UNIT_CD RTL_BSNS_UNIT_CD,'

Line 1165: WHEN ddr_webservices_constants.g_sfid_cd THEN

1161: ||'x.SHIP_AMT SHIP_AMT,'
1162: ||'x.SHIP_AMT_LCL SHIP_AMT_LCL,'
1163: ||'x.SHIP_AMT_RPT SHIP_AMT_RPT';
1164: -- for SALE FORECAST ITEM BY DAY
1165: WHEN ddr_webservices_constants.g_sfid_cd THEN
1166: x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1167: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1168: ||'x.FRCST_NBR FRCST_NBR,'
1169: ||'x.FRCST_TYP FRCST_TYP,'

Line 1189: x_return_status:=ddr_webservices_constants.g_ret_sts_success;

1185: ||'x.FRCST_SLS_AMT FRCST_SLS_AMT,'
1186: ||'x.FRCST_SLS_AMT_LCL FRCST_SLS_AMT_LCL,'
1187: ||'x.FRCST_SLS_AMT_RPT FRCST_SLS_AMT_RPT';
1188: END CASE;
1189: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1190: EXCEPTION
1191: WHEN OTHERS THEN
1192: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1193: x_msg_count:=1;

Line 1192: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1188: END CASE;
1189: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1190: EXCEPTION
1191: WHEN OTHERS THEN
1192: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1193: x_msg_count:=1;
1194: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1195: END get_detail_fact_colms;
1196:

Line 1214: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

1210: BEGIN
1211: --get aggregate columns for the Organization hierarchy based on given hierarchy level code
1212: IF p_exp_org_level IS NOT NULL THEN
1213: get_hrchy_lvl('ORGANIZATION',p_exp_org_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1214: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1215: RETURN;
1216: END IF;
1217: get_org_ref_join(l_lvl_rnk,l_group_col,x_return_status, x_msg_count,x_msg_data);
1218: END IF;

Line 1222: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

1218: END IF;
1219: --get aggregate columns for the location hierarchy based on given hierarchy level code
1220: IF p_exp_loc_level IS NOT NULL THEN
1221: get_hrchy_lvl('LOCATION',p_exp_loc_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1222: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1223: RETURN;
1224: END IF;
1225: get_loc_ref_join(l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1226: IF l_group_col IS NOT NUll THEN

Line 1235: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

1231: END IF;
1232: --get aggregate columns for the Item hierarchy based on given hierarchy level code
1233: IF p_exp_item_level IS NOT NULL THEN
1234: get_hrchy_lvl('ITEM',p_exp_item_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1235: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1236: RETURN;
1237: END IF;
1238: get_item_ref_join(l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1239: IF l_group_col IS NOT NUll THEN

Line 1249: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

1245: --get aggregate columns for the time hierarchy based on given hierarchy level code
1246: IF p_exp_time_level IS NOT NULL THEN
1247: SELECT hrchy_lvl_name INTO l_hrchy_lvl_name FROM DDR_WS_METADATA WHERE hrchy_lvl_cd=p_exp_time_level;
1248: get_hrchy_lvl(l_hrchy_lvl_name,p_exp_time_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1249: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1250: RETURN;
1251: END IF;
1252: get_time_ref_join(l_hrchy_lvl_name,l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1253: IF l_group_col IS NOT NUll THEN

Line 1260: x_return_status:=ddr_webservices_constants.g_ret_sts_success;

1256: l_group_col :=l_ref_join;
1257: END IF;
1258: END IF;
1259: x_group_col:= l_group_col;
1260: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1261: EXCEPTION
1262: WHEN NO_DATA_FOUND THEN
1263: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1264: x_msg_count:=1;

Line 1263: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1259: x_group_col:= l_group_col;
1260: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1261: EXCEPTION
1262: WHEN NO_DATA_FOUND THEN
1263: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1264: x_msg_count:=1;
1265: x_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
1266: WHEN OTHERS THEN
1267: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 1267: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1263: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1264: x_msg_count:=1;
1265: x_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
1266: WHEN OTHERS THEN
1267: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1268: x_msg_count:=1;
1269: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1270: END get_aggr_group_colms;
1271:

Line 1310: l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);

1306: l_file_id :=get_ddr_ws_file_seq_nextval(x_return_status,x_msg_count,x_msg_data);
1307: --construct file name
1308: l_file_name := p_fact_code || '_'|| l_file_id || '.xml';
1309: --open file in write mode
1310: l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);
1311: --write xml header data in the xml file
1312: UTL_FILE.put_line(l_xml_file, '');
1313: --open cursor
1314: l_cur := dbms_sql.open_cursor;

Line 1349: VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N','ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,

1345: BEGIN
1346: INSERT INTO ddr_ws_job_file_dls(file_id, job_id, file_name, status, delete_flag,src_sys_idnt, src_sys_dt, crtd_by_dsr, last_updt_by_dsr,
1347: created_by, creation_date, last_updated_by,
1348: last_update_date, last_update_login)
1349: VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N','ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,
1350: SYSDATE, 101);
1351: END;
1352: l_fetch_rows:=dbms_sql.fetch_rows(l_cur);
1353: IF(l_fetch_rows > 0) THEN

Line 1359: l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);

1355: RAISE l_max_rows;
1356: END IF;
1357: l_file_id :=get_ddr_ws_file_seq_nextval(x_return_status,x_msg_count,x_msg_data);
1358: l_file_name := p_fact_code || '_'|| l_file_id || '.xml';
1359: l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);
1360: UTL_FILE.put_line(l_xml_file, '');
1361: UTL_FILE.put_line(l_xml_file, '');
1362: l_fetch_ctn:=0;
1363: END IF;

Line 1382: VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N',

1378: src_sys_idnt, src_sys_dt, crtd_by_dsr, last_updt_by_dsr,
1379: created_by, creation_date, last_updated_by,
1380: last_update_date, last_update_login
1381: )
1382: VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N',
1383: 'ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,
1384: SYSDATE, 101);
1385: END;
1386: END IF;

Line 1387: x_return_status:=ddr_webservices_constants.g_ret_sts_success;

1383: 'ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,
1384: SYSDATE, 101);
1385: END;
1386: END IF;
1387: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1388: EXCEPTION
1389: WHEN l_max_rows THEN
1390: IF dbms_sql.is_open(l_cur) THEN
1391: dbms_sql.close_cursor(l_cur);

Line 1396: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1392: END IF;
1393: IF UTL_FILE.is_open(l_xml_file) THEN
1394: UTL_FILE.fclose(l_xml_file);
1395: END IF;
1396: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1397: x_msg_count:=1;
1398: x_msg_data:='Program exceeded maximum row OUT NOCOPY put';
1399: WHEN UTL_FILE.INTERNAL_ERROR THEN
1400: IF dbms_sql.is_open(l_cur) THEN

Line 1406: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1402: END IF;
1403: IF UTL_FILE.is_open(l_xml_file) THEN
1404: UTL_FILE.fclose(l_xml_file);
1405: END IF;
1406: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1407: x_msg_count:=1;
1408: x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1409: WHEN UTL_FILE.INVALID_OPERATION THEN
1410: IF dbms_sql.is_open(l_cur) THEN

Line 1416: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1412: END IF;
1413: IF UTL_FILE.is_open(l_xml_file) THEN
1414: UTL_FILE.fclose(l_xml_file);
1415: END IF;
1416: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1417: x_msg_count:=1;
1418: x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1419: WHEN UTL_FILE.INVALID_PATH THEN
1420: IF dbms_sql.is_open(l_cur) THEN

Line 1426: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1422: END IF;
1423: IF UTL_FILE.is_open(l_xml_file) THEN
1424: UTL_FILE.fclose(l_xml_file);
1425: END IF;
1426: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1427: x_msg_count:=1;
1428: x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1429: WHEN UTL_FILE.WRITE_ERROR THEN
1430: IF dbms_sql.is_open(l_cur) THEN

Line 1436: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1432: END IF;
1433: IF UTL_FILE.is_open(l_xml_file) THEN
1434: UTL_FILE.fclose(l_xml_file);
1435: END IF;
1436: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1437: x_msg_count:=1;
1438: x_msg_data:='Cannot write to file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1439: WHEN OTHERS THEN
1440: IF dbms_sql.is_open(l_cur) THEN

Line 1446: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1442: END IF;
1443: IF UTL_FILE.is_open(l_xml_file) THEN
1444: UTL_FILE.fclose(l_xml_file);
1445: END IF;
1446: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1447: x_msg_count:=1;
1448: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1449: END write_fact_to_xml_file;
1450:

Line 1480: IF p_api_version<>ddr_webservices_constants.g_api_version THEN

1476: BEGIN
1477: IF p_api_version IS NULL THEN
1478: RAISE l_api_ver;
1479: END IF;
1480: IF p_api_version<>ddr_webservices_constants.g_api_version THEN
1481: RAISE l_api_ver;
1482: END IF;
1483: IF p_fact_code IS NULL THEN
1484: RAISE l_fact_code_null;

Line 1492: x_return_status:=ddr_webservices_constants.g_ret_sts_success;

1488: END IF;
1489: IF p_org_cd IS NULL THEN
1490: RAISE l_rtl_org_code_null;
1491: END IF;
1492: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1493: EXCEPTION
1494: WHEN l_api_ver THEN
1495: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1496: x_msg_count:=1;

Line 1495: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1491: END IF;
1492: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1493: EXCEPTION
1494: WHEN l_api_ver THEN
1495: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1496: x_msg_count:=1;
1497: x_msg_data:='API version number should not be null';
1498: WHEN l_fact_code_null THEN
1499: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

Line 1499: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1495: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1496: x_msg_count:=1;
1497: x_msg_data:='API version number should not be null';
1498: WHEN l_fact_code_null THEN
1499: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1500: x_msg_count:=1;
1501: x_msg_data:='Fact code should not be null';
1502: WHEN l_mfg_code_null THEN
1503: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

Line 1503: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1499: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1500: x_msg_count:=1;
1501: x_msg_data:='Fact code should not be null';
1502: WHEN l_mfg_code_null THEN
1503: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1504: x_msg_count:=1;
1505: x_msg_data:='Manufacturer Organization code should not be null';
1506: WHEN l_rtl_org_code_null THEN
1507: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

Line 1507: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1503: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1504: x_msg_count:=1;
1505: x_msg_data:='Manufacturer Organization code should not be null';
1506: WHEN l_rtl_org_code_null THEN
1507: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1508: x_msg_count:=1;
1509: x_msg_data:='Retailer Organization code should not be null';
1510: WHEN OTHERS THEN
1511: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 1511: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1507: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1508: x_msg_count:=1;
1509: x_msg_data:='Retailer Organization code should not be null';
1510: WHEN OTHERS THEN
1511: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1512: x_msg_count:=1;
1513: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1514: END validate_input_params;
1515:

Line 1524: x_return_status:=ddr_webservices_constants.g_ret_sts_success;

1520: x_sys_var_val OUT NOCOPY VARCHAR2)
1521: IS
1522: BEGIN
1523: SELECT lkup_name INTO x_sys_var_val FROM ddr_r_lkup_mst WHERE lkup_cd=p_sys_var;
1524: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1525: EXCEPTION
1526: WHEN NO_DATA_FOUND THEN
1527: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1528: x_msg_count := 1;

Line 1527: x_return_status := ddr_webservices_constants.g_ret_sts_error;

1523: SELECT lkup_name INTO x_sys_var_val FROM ddr_r_lkup_mst WHERE lkup_cd=p_sys_var;
1524: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1525: EXCEPTION
1526: WHEN NO_DATA_FOUND THEN
1527: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1528: x_msg_count := 1;
1529: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1530: WHEN OTHERS THEN
1531: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 1531: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1527: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1528: x_msg_count := 1;
1529: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1530: WHEN OTHERS THEN
1531: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1532: x_msg_count:=1;
1533: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1534: END get_sys_var_val;
1535:

Line 1546: x_return_status := ddr_webservices_constants.g_ret_sts_error;

1542: SELECT DDR_WS_FILE_SEQ.NEXTVAL INTO l_next_val FROM dual;
1543: RETURN to_char(l_next_val);
1544: EXCEPTION
1545: WHEN NO_DATA_FOUND THEN
1546: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1547: x_msg_count := 1;
1548: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1549: WHEN OTHERS THEN
1550: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 1550: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1546: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1547: x_msg_count := 1;
1548: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1549: WHEN OTHERS THEN
1550: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1551: x_msg_count:=1;
1552: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1553: END get_ddr_ws_file_seq_nextval;
1554:

Line 1570: --WHEN ddr_webservices_constants.g_rsrid_cd THEN

1566: l_lvl_rnk NUMBER:=null;
1567: BEGIN
1568: -- CASE p_fact_code
1569: -- for RETAIL SALE RETURN ITEM DAY fact
1570: --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1571:
1572: IF p_item_dim_lvl_cd IS NOT NULL THEN
1573: get_hrchy_lvl('ITEM',p_item_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1574: ELSIF p_exp_item_level IS NOT NULL THEN

Line 1611: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1607: END IF;
1608: -- END CASE;
1609: EXCEPTION
1610: WHEN OTHERS THEN
1611: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1612: x_msg_count:=1;
1613: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1614: END get_itm_hrchy_clauses;
1615:

Line 1639: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1635: x_ref_join:=' ITMF.MFG_DIV_CD ';
1636: END CASE;
1637: EXCEPTION
1638: WHEN OTHERS THEN
1639: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1640: x_msg_count:=1;
1641: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1642:
1643: END get_item_ref_join;

Line 1661: --WHEN ddr_webservices_constants.g_rsrid_cd THEN

1657: l_lvl_rnk NUMBER:=null;
1658: BEGIN
1659: -- CASE p_fact_code
1660: -- for RETAIL SALE RETURN ITEM DAY fact
1661: --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1662: IF p_org_dim_lvl_cd IS NOT NULL THEN
1663: get_hrchy_lvl('ORGANIZATION',p_org_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1664: ELSIF p_exp_org_level IS NOT NULL THEN
1665: get_hrchy_lvl('ORGANIZATION',p_exp_org_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);

Line 1705: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1701: END IF;
1702: -- END CASE;
1703: EXCEPTION
1704: WHEN OTHERS THEN
1705: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1706: x_msg_count:=1;
1707: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1708: END get_org_hrchy_clauses;
1709:

Line 1733: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1729: x_ref_join:=' ORGG.CLSTR_CD ';
1730: END CASE;
1731: EXCEPTION
1732: WHEN OTHERS THEN
1733: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1734: x_msg_count:=1;
1735: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1736: END get_org_ref_join;
1737:

Line 1755: --WHEN ddr_webservices_constants.g_rsrid_cd THEN

1751: l_hrchy_lvl_name VARCHAR2(50):=null;
1752: BEGIN
1753: -- CASE p_fact_code
1754: -- for RETAIL SALE RETURN ITEM DAY fact
1755: --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1756: --to get the time hierarchy level name(GREGORIAN TIME OR BUSINESS TIME)
1757: IF p_time_dim_lvl_cd IS NOT NULL THEN
1758: SELECT hrchy_lvl_name INTO l_hrchy_lvl_name FROM DDR_WS_METADATA WHERE
1759: hrchy_lvl_cd=p_time_dim_lvl_cd;

Line 1821: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1817: END CASE;
1818: -- END CASE;
1819: EXCEPTION
1820: WHEN OTHERS THEN
1821: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1822: x_msg_count:=1;
1823: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1824: END get_time_hrchy_clauses;
1825:

Line 1862: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1858: END CASE;
1859: END CASE;
1860: EXCEPTION
1861: WHEN OTHERS THEN
1862: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1863: x_msg_count:=1;
1864: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1865: END get_time_ref_join;
1866:

Line 1885: --WHEN ddr_webservices_constants.g_rsrid_cd THEN

1881: l_org_lvl_rnk NUMBER:=null;
1882: BEGIN
1883: -- CASE p_fact_code
1884: -- for RETAIL SALE RETURN ITEM DAY fact
1885: --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1886: IF p_loc_dim_lvl_cd IS NOT NULL THEN
1887: get_hrchy_lvl('LOCATION',p_loc_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1888: ELSIF p_exp_loc_level IS NOT NULL THEN
1889: get_hrchy_lvl('LOCATION',p_exp_loc_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);

Line 1918: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1914: END IF;
1915: -- END CASE;
1916: EXCEPTION
1917: WHEN OTHERS THEN
1918: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1919: x_msg_count:=1;
1920: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1921: END get_loc_hrchy_clauses;
1922:

Line 1940: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1936: x_ref_join:=' LOCD.CNTRY_CD ';
1937: END CASE;
1938: EXCEPTION
1939: WHEN OTHERS THEN
1940: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1941: x_msg_count:=1;
1942: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1943: END get_loc_ref_join;
1944:

Line 1959: x_return_status := ddr_webservices_constants.g_ret_sts_error;

1955: -- DBMS_OUTPUT.PUT_LINE('p_hrchy_lvl_name='||p_hrchy_lvl_name||' ,p_hrchy_lvl_cd='||p_hrchy_lvl_cd||' ,x_hrchy_lvl='||l_hrchy_lvl);
1956: x_hrchy_lvl:=l_hrchy_lvl;
1957: EXCEPTION
1958: WHEN NO_DATA_FOUND THEN
1959: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1960: x_msg_count := 1;
1961: x_msg_data := 'No data found for hierarchy code:'|| p_hrchy_lvl_cd ||'. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1962: END get_hrchy_lvl;
1963:

Line 1976: IF p_fact_code= ddr_webservices_constants.g_sfid_cd THEN

1972: l_max_frcst_date DATE:=null;
1973: BEGIN
1974: --if the fact code is for forcest sales table, then join condition to fetch
1975: --the latest forcest version
1976: IF p_fact_code= ddr_webservices_constants.g_sfid_cd THEN
1977: --bug 6905930 change start
1978: l_join_cndn:= ' AND (x.frcst_vrsn,x.mfg_org_cd,x.rtl_org_cd,
1979: x.org_bsns_unit_id, x.day_cd, x.glbl_item_id, x.rtl_sku_item_id) IN(SELECT
1980: MAX(frcst_vrsn), mfg_org_cd, rtl_org_cd, org_bsns_unit_id, day_cd,

Line 1989: x_return_status := ddr_webservices_constants.g_ret_sts_error;

1985: END IF;
1986: x_oth_join_codn := l_join_cndn;
1987: EXCEPTION
1988: WHEN NO_DATA_FOUND THEN
1989: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1990: x_msg_count := 1;
1991: x_msg_data := 'No data found. Error Code' ||sqlcode||' Error
1992: message:'||sqlerrm;
1993: WHEN OTHERS THEN

Line 1994: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1990: x_msg_count := 1;
1991: x_msg_data := 'No data found. Error Code' ||sqlcode||' Error
1992: message:'||sqlerrm;
1993: WHEN OTHERS THEN
1994: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1995: x_msg_count:=1;
1996: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:
1997: '||sqlerrm;
1998: END get_other_join_conditions;