28: ToDate IN VARCHAR2)
29: IS
30: TYPE refcur IS REF CURSOR;
31: l_refcur refcur;
32: l_result XMLType;
33: l_final_clob CLOB ;
34: l_len number;
35: l_xml_data varchar2(10);
36: l_limit number;
64: OPEN GET_ORG_NAME;
65: FETCH GET_ORG_NAME into l_org_name, l_org_code;
66: CLOSE GET_ORG_NAME;
67:
68: l_argument_string := 'select XMLELEMENT("DispenseDispatch", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''
69: XMLTYPE(''
70: XMLTYPE(''
71: XMLTYPE(''
72: XMLTYPE(''
65: FETCH GET_ORG_NAME into l_org_name, l_org_code;
66: CLOSE GET_ORG_NAME;
67:
68: l_argument_string := 'select XMLELEMENT("DispenseDispatch", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''
69: XMLTYPE(''
70: XMLTYPE(''
71: XMLTYPE(''
72: XMLTYPE(''
73: )),
66: CLOSE GET_ORG_NAME;
67:
68: l_argument_string := 'select XMLELEMENT("DispenseDispatch", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''
69: XMLTYPE(''
70: XMLTYPE(''
71: XMLTYPE(''
72: XMLTYPE(''
73: )),
74: XMLAGG(XMLELEMENT("DispatchDetails",
67:
68: l_argument_string := 'select XMLELEMENT("DispenseDispatch", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''
69: XMLTYPE(''
70: XMLTYPE(''
71: XMLTYPE(''
72: XMLTYPE(''
73: )),
74: XMLAGG(XMLELEMENT("DispatchDetails",
75: XMLFOREST(:orgName as OrganizationName,
68: l_argument_string := 'select XMLELEMENT("DispenseDispatch", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''
69: XMLTYPE(''
70: XMLTYPE(''
71: XMLTYPE(''
72: XMLTYPE(''
73: )),
74: XMLAGG(XMLELEMENT("DispatchDetails",
75: XMLFOREST(:orgName as OrganizationName,
76: RES.subinventory_code as Location ,
205: OperatorID IN VARCHAR2)
206: IS
207: TYPE refcur IS REF CURSOR;
208: l_refcur refcur;
209: l_result XMLType;
210: l_final_clob CLOB ;
211: l_len number;
212: l_xml_data varchar2(10);
213: l_limit number;
239: OPEN GET_ORG_NAME;
240: FETCH GET_ORG_NAME into l_org_name, l_org_code;
241: CLOSE GET_ORG_NAME;
242:
243: l_argument_string := 'select XMLELEMENT("DispenseHistory", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''
244: XMLTYPE(''
245: XMLTYPE(''
246: XMLTYPE(''
247: XMLTYPE(''
240: FETCH GET_ORG_NAME into l_org_name, l_org_code;
241: CLOSE GET_ORG_NAME;
242:
243: l_argument_string := 'select XMLELEMENT("DispenseHistory", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''
244: XMLTYPE(''
245: XMLTYPE(''
246: XMLTYPE(''
247: XMLTYPE(''
248: XMLTYPE(''
241: CLOSE GET_ORG_NAME;
242:
243: l_argument_string := 'select XMLELEMENT("DispenseHistory", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''
244: XMLTYPE(''
245: XMLTYPE(''
246: XMLTYPE(''
247: XMLTYPE(''
248: XMLTYPE(''
249: )),
242:
243: l_argument_string := 'select XMLELEMENT("DispenseHistory", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''
244: XMLTYPE(''
245: XMLTYPE(''
246: XMLTYPE(''
247: XMLTYPE(''
248: XMLTYPE(''
249: )),
250: XMLAGG(XMLELEMENT("DispenseHistoryDetails",
243: l_argument_string := 'select XMLELEMENT("DispenseHistory", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''
244: XMLTYPE(''
245: XMLTYPE(''
246: XMLTYPE(''
247: XMLTYPE(''
248: XMLTYPE(''
249: )),
250: XMLAGG(XMLELEMENT("DispenseHistoryDetails",
251: XMLFOREST(:orgName as OrganizationName ,
244: XMLTYPE(''
245: XMLTYPE(''
246: XMLTYPE(''
247: XMLTYPE(''
248: XMLTYPE(''
249: )),
250: XMLAGG(XMLELEMENT("DispenseHistoryDetails",
251: XMLFOREST(:orgName as OrganizationName ,
252: GMDL.subinventory_code as Location ,