********************************************************************
Launch a concurrent request from a stored procedure in synchronous mode:
********************************************************************
See procedure: hot_launch_edi_erp_850_pdf
********************************************************************
Get request id for generation of filename for output files:
********************************************************************
SELECT 'R' || TO_CHAR (fnd_global.conc_request_id) || '.csv'
INTO v_filename
FROM DUAL;
********************************************************************
Get database name in stored procedures:
********************************************************************
--
-- Get database currently logged into
--
SELECT NAME
INTO v_database
FROM v$database;
IF v_database = 'PROD'
THEN
v_database := NULL;
ELSE
v_database := v_database || ' ';
END IF;
********************************************************************
Create multi column primary key:
********************************************************************
CREATE TABLE group_members (
GROUP_ID VARCHAR2(10) REFERENCES csr_groups(GROUP_ID),
user_id NUMBER REFERENCES csr_users(user_id),
CONSTRAINT pk_group PRIMARY KEY (GROUP_ID, user_id));
********************************************************************
Create a date field for a concurrent request:
********************************************************************
v_from_date DATE;
v_to_date DATE;
--
-- Convert dates
--
v_from_date := TO_DATE (SUBSTR (p_week_ending_low, 1, 10), 'YYYY/MM/DD');
v_to_date := TO_DATE (SUBSTR (p_week_ending_high, 1, 10), 'YYYY/MM/DD');
********************************************************************
Set up a warehouse parameter in a concurrent request:
********************************************************************
Use the “HOT_ORGANIZATIONS” value set. The 3 letter org will appear on the lov but the org_id
number will be passed on.
********************************************************************
Remove carriage returns in a unix file taken from a dos environment:
********************************************************************
While in vi, press the colon and enter followed by a return
:1,$s/<Ctl-v><Enter>//g
********************************************************************
Set the default org and responsibility on a concurrent request
********************************************************************
********************************************************************
Set the default org and responsibility on a concurrent request
********************************************************************
Where the Default Values reads:
SELECT user_name FROM fnd_user WHERE user_id = TO_NUMBER(fnd_profile.value('USER_ID'))
** Within report or procedure, can use FND_PROFILE.VALUE(‘ORG_ID’) to get org_id
********************************************************************
Set the who created and who updated fields in a form.
********************************************************************
In the pre-insert and pre_update triggers, insert the following line:
fnd_standard.set_who;
********************************************************************
Get warehouses in operating unit.
********************************************************************
SELECT organization_id
FROM hotus.hot_dm_org_extract_v
WHERE operating_unit = 338
********************************************************************
Get rid of duplicates.
********************************************************************
DELETE FROM hot_cph_upload
WHERE a.ROWID >
ANY (SELECT b.ROWID
FROM hot_cph_upload b
WHERE a.po_number = b.po_number
AND a.folio_no = b.folio_no
AND a.order_delivery = b.order_delivery);
********************************************************************
Force Warning in PL/SQL concurrent request.
********************************************************************
From the package: WSH_SHIP_CONFIRM_ACTIONS
procedure interface_ALL(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_mode IN VARCHAR2,
p_stop_id IN NUMBER,
p_delivery_id IN NUMBER,
p_log_level IN NUMBER,
p_batch_id NUMBER DEFAULT NULL,
p_trip_type IN VARCHAR2 DEFAULT NULL) IS
l_completion_status VARCHAR2(30);
l_temp BOOLEAN;
......
IF l_stop_id IS NULL THEN
fnd_file.put_line(FND_FILE.LOG,'InterfaceTripStop: cannot find stop. Exiting.');
l_completion_status := 'WARNING';
goto interface_end;
END IF
.......
<<interface_end>>
l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
IF l_completion_status = 'NORMAL' THEN
errbuf := 'Interface trip stop is completed successfully';
retcode := '0';
ELSIF l_completion_status = 'WARNING' THEN
errbuf := 'Interface trip stop is completed with warning';
retcode := '1';
ELSE
errbuf := 'Interface trip stop is completed with error';
retcode := '2';
END IF;
********************************************************************
Force Warning in RDF concurrent request.
********************************************************************
v_ret BOOLEAN;
begin
srw.message(22,to_char('Finishing with warning'));
v_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING','');
********************************************************************
Set check boxes to “select all” or “unselect all”.
********************************************************************
See form HOT_855_ALL.fmb
********************************************************************
Programmatically see listing for directory.
********************************************************************
SELECT column_value
FROM TABLE (hot_list_files ('/app/gentranedi/TEST/inbound'))
WHERE column_value NOT IN ('failed', 'processed')
Where column_value is 255 characters long.
********************************************************************
Prepare rdf for apps.
********************************************************************
Before moving the reports to application do the following to make sure that the report is Oracle
Applications compliant.
1. Remove the '_all' tables from the FROM clause. For instance instead of using PA_PROJECTS_ALL table
use PA_PROJECTS (view).
2. Add a User Parameter P_CONC_REQUEST_ID. The datatype of this parameter is Number , Size is 15,
initial value is 0
3. In the BEFORE REPORT Trigger add this piece of code to set the environment variables.
SRW.USER_EXIT('FND SRWINIT');
4. In the AFTER REPORT trigger add this piece of code to reset the environment variables.
SRW.USER_EXIT('FND SRWEXIT');
********************************************************************
Assign tablespace for data and index.
********************************************************************
CREATE TABLE hotic_br_cat_override (
customer_trx_id NUMBER,
period_name VARCHAR2(15),
company VARCHAR2(100),
division VARCHAR2(100),
org NUMBER,
brand VARCHAR2(100),
CATEGORY VARCHAR2(100),
CONSTRAINT hotic_brcatovrde_pk PRIMARY KEY (customer_trx_id) USING INDEX TABLESPACE
hotusx
)
TABLESPACE hotusd;
********************************************************************
Make exception handler.
********************************************************************
--
-- Vefify that year for customer has been defined
--
IF v_count = 0
THEN
raise_application_error (-20000,
'Start date for year '
|| TO_CHAR (p_year)
|| ' is not defined for custoemer: '
|| p_customer
);
END IF;
********************************************************************
Form button trigger to get user, loop through items and suppress commit messages
********************************************************************
DECLARE
v_total NUMBER := 0;
v_user NUMBER := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
BEGIN
IF :control.carton IS NULL THEN
SHOWMESSAGE('You must enter a carton number first.');
RETURN;
END IF;
GO_BLOCK('OE_ORDER_LINES_ALL');
FIRST_RECORD;
IF :OE_ORDER_LINES_ALL.LINE_NUMBER IS NOT NULL THEN
--
-- At least one record was found
--
LOOP
IF :OE_ORDER_LINES_ALL.ALLOCATE = 'T' THEN
INSERT INTO hotus.hot_karina_label_details (
DELIVERY_ID,
LINE_ID,
CARTON_NO,
QUANTITY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES(
:CONTROL.DELIVERY_ID,
:OE_ORDER_LINES_ALL.LINE_ID,
:CONTROL.CARTON,
:OE_ORDER_LINES_ALL.DSP_QTY,
SYSDATE,
v_user,
SYSDATE,
v_user);
:OE_ORDER_LINES_ALL.ALLOCATE := 'F';
--
-- Quiet Commit
--
:System.Message_Level := '20';
COMMIT;
:System.Message_Level := '0';
END IF;
EXIT WHEN :System.Last_Record = 'TRUE';
NEXT_RECORD;
END LOOP;
EXECUTE_QUERY;
END IF;
END;
********************************************************************
Warehouse and division queries.
********************************************************************
SELECT organization_id, organization_code FROM HOTEDI.HOT_ORGANIZATIONS;
SELECT organization_id, name
FROM HR_ALL_ORGANIZATION_UNITS
WHERE TYPE = 'DIVISION'
ORDER BY 1
